A Date function? 1

[Stretchy]

I have a table containing different dates for each dpeartment. Each date corresponds to a specific work target they have to work for. I want a text box on my form to show the target date which is current. I just haven't worked out how to implement this. Any Ideas?

thx for any help

_______________
Stretchy

 

[SgtJarrow]

Assumption1: your date table is set up something like this
department targetdate
billing 06/01/02
accounting 05/01/02

Assumption2: your form (or report) where you want to display the date already contains the department field

Simply add a text box to the form, set the control source to the targetdate and your set. As you change the departments, the date will change too. I also recommend disabling and/or locking the field, so the users can't change the date on you.

If I am wrong, or you need more help, please feel free to ask. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[Stretchy]

That is exactly what i need yes . However, the target dates (past, present and future) are shown in a list box on the form so that the user can browse departments through a combo box and the list box will update. What I need is the afforementioned text box to display the current target and not the past or future target, and obviously update when the future date becomes the present. Hope you understand.

Thx for your help _______________
Stretchy

 

[SgtJarrow]

I sort of understand.....how do you determine the "current" target date" If you have a list box that list "all" target dates, then you are already 75% there.....just need to knnow how to tell if somethign is current, past or present and we can finish this up. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[Stretchy]

Thats exactly the problem. Basically the text box needs to know that the current target is the one the departments will follow until the next target. I suppose I need to say something like

If Now() < Me.TargetDate then
Me.Text1 = Me.TargetDate

theres probably more but I cant think what it should be

thx again _______________
Stretchy

 

[SgtJarrow]

So what I think you want is the text box to display the target date form the list box that is the closest future date to today....right??? For example, today is 5/7 and your list box shows 5/1, 5/5, 5/9, 5/13, 5/15. You want the text box to show 5/9? I really can't picture what needs to be done at this point. Can you send the database so I can look at it??? Programming isn't a profession of choice.
It's a profession of calling...
&quot;Hey Programmer, your application broke again!&quot;

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[Stretchy]

I cant send it no.... corporate data. You are accurate in your assumptions though, that is exactly what i want it to do.

thx again
_______________
Stretchy

 

[SgtJarrow]

Stretchy......

I have it for you.....it is in a sample database you can use to make yours work....give me an address and I'll send it directly.

That was a fun one... Programming isn't a profession of choice.
It's a profession of calling...
&quot;Hey Programmer, your application broke again!&quot;

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[Stretchy]

Amazing... thx m8
send to stretch21@lineone.net and thx again for all your help. _______________
Stretchy

 

[Stretchy]

Thx for the material. There is one question i have to ask. I take it you are using Access97. I cannot seem to find the code you used to update the text box with the date. I've checked in the properties etc and vb code editor but cannot find it anywhere.. Is it all SQL based? _______________
Stretchy

 

[SgtJarrow]

Nope....there was no code used to do this...

I built the table and enetered some sample data. SImlpe enough. Then I created the form. I began with the combo box. I used the combo box wizard, selected the department as the field and finished the wizard. I then went into the record source for the combo box by clicking the ellipse (3 dots) at the end of that property line. In the first BLANK field, I right clicked and select properties. I made sure the Unique Value property was set to yes. I now will only see one of each department. Next, I built the list box. Used the same procedure as the combo box mostly, selecting the department and date in the wizard. After the wizard was done, I set the department field width to 0, and in the row source for the list box, I set the department equal to the combo box value and the date to anything greater than or equal to today.

The last part, the text box was tricky. I at first tried a few recordset combinations, but I couldn't get it to work. Then I remebered a litle used feature of the query. I built the query, made the departemnt equal to the combo box, and the date anything greater than or equal to today. I then right clicked in the first BLANK field and selected properties. There is a property called TopValues. Noramlly this is set to all, so Access displays all records that meet the criteria. You can set this to a percentage or an exact number. I made sure the dates were in ascending order, and set the TopValue to 1....making Access only give me the first record it found.

To tie it all together, I used the combo box AfterUpdate property to requery everything every time it was changed.

Hope this makes it clearer for you....if not, please let me know. Also, if you need an SQL statement or something for this, I can get it for you from the database. Just let me know. Programming isn't a profession of choice.
It's a profession of calling...
&quot;Hey Programmer, your application broke again!&quot;

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com