Full Version: Date Problem
UtterAccess Forums > Microsoft® Access > Access Forms
Pages: 1, 2
drmojo418
ok i have form that has 2 unbound fields on it that i put my dates in to get the data that i need. in the the subform that is on the main form in the criteria of the date field i have the following code:
etween [Forms]![CenterPerformanceReport]![txtStartDt] And [Forms]![CenterPerformanceReport]![txtEndDt]
the unbound fields on the form are named txtstartdt and txtenddt. now here is my problem the dates that i am pulling from in the table are set up like so:
10/18/2004 12:54:25 PM
There is what is so messed up about it all. when i put 10/08/04 in txtstartdt and 10/16/04 in txtenddt. it pull the info for 10/01/2004, 10/11, 10/12, 10/13, 10/14, and 10/15.
but if i put the dates in like this:
10/8/2004 in txtstartdt
10/16/2004 in txtenddt
it pulls dates for 10/4, 10/5, 10/6, 10/7, 10/8, and 10/16.
why is this doing this and what are my options to fix it.
thanks
chad
erwardell
After you update the date in any of the unbound fields you need to leave those controls. If you leave the focus on after you update it and not go to another text box or the previouse text box it will not properly update. I am not sure how to explain it well enough other than update the text box then get out of it to set it.
Jack Cowley
The problem is the time in the field with the date. I would suggest you store the time, if you need to save it, in a separate field and then this will eliminate the problem. You can use code like this:
eft([txtStartDate],InStr(1,[txtStartDate]," ")-1)
to get just the date part of the field...
hth,
Jack
R. Hicks
I disagree ... if the Date and Time is needed within the app .. keep it in one field.
It is very simple to parse the Date or the Time from the data if and when it is needed.

Int([DateAndTimeValue]) .. or .. DateValue([DateAndTimeValue]) .. will return just the Date value ...
[DateAndTimeValue] - Int([DateAndTimeValue]) .. or .. TimeValue([DateAndTimeValue]) .. will return just the Time value ...

RDH
Jack Cowley
Ricky -

HAs always, an excellent solution! May your winters be mild and your summers cool with low humidity...

Jack
Edited by: Jack Cowley on Mon Oct 18 20:11:52 EDT 2004.
drmojo418
The int won't work for some reason and i am already using the datevalue. still nothing happening
Thanks
chad
R. Hicks
Post the expression .. or code you are using ...
DH
Jack Cowley
Try:
Date(Int(YourDate))
I have to be carefully as Ricky is very clever and seldom makes a mistake, but as his code is written it does not return what we know as a date, but to Access it is a date....
Jack
drmojo418
Date: DateValue([CRCallDateTime])
That is what i am useing. the problem i am having is not that it isn't returning a date the problem is that it is returning the wrong date.
R. Hicks
We need to see how and where it is being used ...
The CDate() should not be needed .. as a Date/Time datatype is stored internally as a numeric double value in Access .. the value to the left of the decimal is the Date value .. and the fractional value to the right of the decimal is the Time value ...
How it is displayed is dependant on the formatting ...
RDH
R. Hicks
"Date" is a "Resevered Word" and more than likely causing the problem ...
Change your expression name to "MyDate"" ...

RDH
drmojo418
before the datevalue:
0/8/2004 6:23:45 PM
after the datevalue:
10/8/2004
is that what you were asking?
drmojo418
that didn't fix it. still doing what i origianly said.
R. Hicks
What are you after ???
That you have will parse the date value from a Date and Time value ...
You then simple set the criteria you need in this field expression in your query ...
RDH
Jack Cowley
Ricky -
otcha! I new the Int value was the date, but Int([MyDate]) returns an integer, as you know, unless the formatting is Date.... Lordy, lordy... Here is what happened.. I was on my way to the wine cellar and I tripped on the cat, fell down the stairs and forget why I was in the wine cellar when I came to. So I went online and my messages to this post are the result of that sudden stop...
Please take over while I return to the wine cellar for some medications I keep down there....
Jack
drmojo418
what the problem is that on my main form i have 2 unbound text boxes. one called "txtstartDt" and one called "txtendDt". and a button next to them that requeries the subforms on my main form to show the info for the dates that i put in the boxes. but when i put in (for example) 10/08/04-10/16/04 it show me the data for 10/01/04 and 10/11/04-10/15/04 but not 10/08/04. and i know there is data for 10/08/04.
R. Hicks
LMAO ... it could only happen to you Jack ...

I hope you are OK and you did not break anything (ie ... bottle of wine) ...

RDH
R. Hicks
Where are you placing the criteria ???
It should be placed in the Field expression that parses the Date from the Date and Time value ...
And ... post what you are using as the criteria ...
DH
Jack Cowley
Ricky -
MAO too! Glad you understand and NOTHING was broken, thank goodness! Am arm or a leg is one thing, but a nice bottle of Zinfandel would be a real disaster....
Stay well and thanks for always being there at the right times...
Jack
drmojo418
here is a screen shot of my form sorry but for legal purposes i had to get rid of most of the data.
the main thing to look at is the dates at the top and the dates on the side. and also at the bottom is the query it is reading off of.
R. Hicks
Possibly the values are not formatted the same ...
ry:
CODE
Between DateValue([Forms]![CenterPerformanceReport]![txtStartDt]) And DateValue([Forms]![CenterPerformanceReport]![txtEndDt])

.... and change the expression name to "MyDate" as I posted earlier ...
RDH
drmojo418
let me make sure i understand you correctly.

on the feild name in the query take out the field name and leave it as crcalldatetime and change the criteria to

Between DateValue([Forms]![CenterPerformanceReport]![txtStartDt]) And DateValue([Forms]![CenterPerformanceReport]![txtEndDt])

scratch that it worked that time i just had it in the wrong criteria field.
Edited by: drmojo418 on Mon Oct 18 20:59:02 EDT 2004.
Edited by: drmojo418 on Mon Oct 18 21:02:14 EDT 2004.
drmojo418
i want to thank you and jack for all the time and (accidents) that you both put in on this issue. i would be lost with out both of your help.
Thank again
chad
R. Hicks
No ... I mean you have named your Field expression "Date".
HAs I posted earlier .. that is a Reserved Word in Access .. change the Field expression name to "MyDate:"
o your Field Expression should be:
CODE
MyDate: DateValue([CRCallDateTime])

And the criteria for this field expression .. try:
CODE
Between DateValue([Forms]![CenterPerformanceReport]![txtStartDt]) And DateValue([Forms]![CenterPerformanceReport]![txtEndDt])

RDH
drmojo418
See above post
R. Hicks
Glad you finally got it ... frown.gif
DH
drmojo418
after further review that cause a major problem.
The words in red are what caused the problem:
Between DateValue([Forms]![CenterPerformanceReport]![txtStartDt]) And DateValue ([Forms]![CenterPerformanceReport]![txtEndDt])
before i put datevalue in the code i could open my main form then put a date in and have it populate the subforms but since i put datevalue in the code the main form errors out and won't let me put a date in the main form to requery the subforms.
drmojo418
here is the error that it is causing when i open up the main form.
Jack Cowley
If txtStartDt and txtEndDt only have dates (i.e. 9/23/2004) then you should not need the DateValue() code.
oes that fix the problem? You only need the DateValue if your date contains the Date and Time. Also, in your query is the criteria in a column that has both date and time? If so then I would add a column to the query using code suggested by Mr. Hicks so that only a date is shown in that column and then put your Between And code in that column, not the column with date and time.
I hope I am making sense and that I understand your problem.
Jack
drmojo418
i am currently useing the code that mr hicks said and that is what is causing the problem.
Jack Cowley
Let me start at the beginning... You have a query that you are trying to filter by dates and the dates are in this format: 1/1/2004 11:23:34pm. Am I correct so far? Next I assume you have a form with two unbound fields where you enter dates and you want those dates to be the criteria for the date fields but the date fields are in the format above and you get inconsistent results. Am I still on track?
If I am on track then add a column to your query and put this in the top line:
nDate: Left([NameOfDateField],InStr(1,[NameOfDateField]," ")-1)
Put the name of your date field that has the date and time in the place that says, NameOfDateField. In the criteria line of this column put your Between And code and see if this returns the records you want.
Jack
R. Hicks
Jack .. the result of that expression will be a string value .. and that can cause problems at times.
The output needs to be a true Date/Time datatype ...

My guess is that there is another underlying problem here that we are not seeing.
What he needs can be done using the DateValue() function as I posted earlier to parse and output the Date and Time data as only a Date as Date/Time datatype.

RDH
drmojo418
i have the date/time field parsed so that i only shows the date in my query that i am using in my subform. so if i was to just run my query for the subform it only shows the date.
Everything worked (somewhat) up until i put the datevalue in the criteria string of the query. before i put the datevalue in the string if i didn't put any date in the popup boxes it would just show a blank query. but after i put it in the string it shows me the error message.
and the problem i am having with me my main form that holds all my subforms lies with in the string in the subform. i think the subforms are trying to open before i can put a date in there which in return gives me that error.
R. Hicks
As Jack posted ... the DateValue() should not be needed in the criteria line .. as long as the input is in the same format as the field ...

And .. as I posted ealier .. it appears to me that you have something else going on here that is creating your current problem.
Without seeing the database .. we can only guess at any possible hidden underlying problem you may have ...

RDH
drmojo418
let me see if i can make a sample database and then post it for you
drmojo418
there really isn't a way that i can put any part of the database up on there. so lets start from scratch.
I have a table that is linked to my database that shows all sales made for everyday.
Othen have a query that is based off the table (the reason for the query is because there are fields that are text that need to be number or currency but can't be changed do to the way that scripts are set up in our corporate office). i then have a queries that are build off my salestable query. in the queries that based off the salestable query i have them set up to give me totals of number of sales, hours and it also shows the date that the totals were made on. Then from those queries i have form set up so i can make them into subforms to be put on a main form (i have 13 subforms on my mainform).
the way that my queries are set up are as shown in the picture in the attachment. and also the way that the date field is set up in the table.
like i was saying before everything worked fine to a point before i added the datevalue to the criteria string of the query.
There is the old criteria:
Between ([Forms]![CenterPerformanceReport]![txtStartDt]) And ([Forms]![CenterPerformanceReport]![txtEndDt])
here is the criteria after the change:
Between DateValue([Forms]![CenterPerformanceReport]![txtStartDt]) And DateValue([Forms]![CenterPerformanceReport]![txtEndDt])
that is the only change that i have made to the whole database since we have started this tread.
before i put the datevalue in i could open my main form and then put in the dates and have it reflect in my subforms. the only problem was when ever there was a date that wasn't double digits (for example: 10/8/04-10/16/04) it would show me results for everything but the 8th so it would show me the 11th-16th. and instead of showing the 8th or 9th it would show me the 10/1/04 instead.
and after i put the datevalue in the criteria i would get the prementioned error. when ever i try to open my main form.
i believe this is steming from trying to open the subforms with out having put in a date first before opening the main form.
i hope this all made sense.
thanks
chad
Jack Cowley
Just for fun use this code as your criteria in the query you show in your attachment:
etween ([Forms]![CenterPerformanceReport]![txtStartDt]) And ([Forms]![CenterPerformanceReport]![txtEndDt])
Put this in the column in your query that has this as the top line:
mydate: Left([CRCallDateTime], Instr(1,[CrCallDateTime]," ") -1)
Fill in your date controls on the form then go to the query (leaving the form open) and open it and see if it returns the records you expect it to return.
Jack
drmojo418
it doesn't return anything!
Jack Cowley
CrCallDateTime is a date and a time in the format 1/1/2004 10:2030pm correct? If you remove the criteria and run the query does the MyDate column show dates in the format 1/1/2004? If not, what is the format, if any?
ack
drmojo418
yes on both accounts
Jack Cowley
Were the dates in your form dates in the format 1/1/2004 and were they dates that exist in the table? If yes and the query is NOT returning any records then make a new query with just the one field, as you showed in your attachment, and see if that query returns records....

Also, check the spelling of your form and control names in the criteria...

Edited by: Jack Cowley on Wed Oct 20 19:05:26 EDT 2004.
drmojo418
still nothing
drmojo418
sorry to be such a headache but i just don't understand why it doesn't work.
Jack Cowley
I am doing some testing...
Jack Cowley
Open the demo and put in 1/1/2004 and 1/13/2004 in the start and end date controls. click the query button. You should see 5 records, all there are in the table. Look at the query to see how I did it...
th,
Jack
drmojo418
ok works fine but do me a favor now. run your query but don't put any dates in and read the error that you get. that is my problem. becuase the subforms are trying to open on the main form on my database before i even have a chance to put in any dates
drmojo418
here is your database back i set it up like what i need it to be like. and now you can see my problem.
Jack Cowley
Now I see what your problem is... You have the cart before the horse. A couple of things you can do.. Popup up a small form and fill in the dates then open the form based on the query. You can also create an SQL statement and create a saved querydef and set the subforms RecordSource to the saved querydef or just create the query and set the subforms RecordSource to the SQL. Something like:
!--c1-->
CODE
SELECT DateValue([MyDates]) AS mydate
FROM tblMyDates
WHERE (((DateValue([MyDates])) Between DateValue([Forms]![Form1]![StartDate]) And DateValue([Forms]![Form1]![EndDate])));

What do you think?
Jack
drmojo418
k i will try that and get back with you.

thanks and sorry i wish i could have explained that much easier to start off with.

Oreally hate to ask this but could you lead me in the right direction on the popup i have never delt with popup boxes before.

thanks again for ALL the time you have spent on this.

chad
Edited by: drmojo418 on Wed Oct 20 20:05:39 EDT 2004.
Jack Cowley
I think you did mention the fact that the subform was based on a query that was getting the data from the main form. We got involved with the fact that you were having trouble returning all the dates...
emove the Record Source for yoursubforms form. Add a button to you form with code like this:
Me.YourSubformControlName.Form.RecordSource = "NameOfYourQuery"
Now fill in the dates and click your button. The subform will show the results.
Good luck...
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.