convert text to date

[fanfan2]

Hi, I get the user input from a text box in a form, then i use it in the query.

in my query i used the DateDiff function in the expression. should I convert the data type from string (the user input from the text box) to date type first before using the DateDiff function?

in the SQL view, I wrote the following, but it says incorrect:

SELECT availability.machineName,
(DateDiff("d",CDate(Forms!frmMain!txtStartDate),CDate(Forms!frmMain!txtEndDate))+1)*24 AS totalMonthlyHours, availability.type
FROM availability
WHERE (availability.date)>=CDate(Forms!frmMain!txtStartDate) And (availability.date)<=CDate(forms!frmMain!txtEndDate)
GROUP BY availability.machineName, availability.type;

Thanks in advance.

 

[Brianwarnock]

For Cdate to work the string has to look like a date in the first place, so why not do what everybody else does, format your input textboxes as dates perhaps even using an input mask.

Brian

 

[fanfan2]

Thanks.

Your right. Now I formatted the text boxes to date. then changed the SQL:

SELECT availability.machineName,
(DateDiff("d",Forms!frmMain!txtStartDate,Forms!frmMain!txtEndDate)+1)*24 AS totalMonthlyHours, availability.type
FROM availability
WHERE (availability.date)>=Forms!frmMain!txtStartDate And (availability.date)<=forms!frmMain!txtEndDate
GROUP BY availability.machineName, availability.type;

Then I entered the dates from the text boxes, then went back to the query, it didn't seem to have captured any information from the inputs - I got empty rows for the query ( im sure the format of my input was right and the date range was within my data).

I've been trying to make this to work for the whole afternoon yesterday and the whole morning today, but didn't make it.

Thanks very much for any help.

 

[Brianwarnock]

I'm surprised it ran at all as I thought that it would flag
(DateDiff("d",Forms!frmMain!txtStartDate,Forms!frm Main!txtEndDate)+1)*24 AS totalMonthlyHours

as not part of an aggregate function or words to that effect.

but other than that cannot see a problem.

Brian

 

[Jon K]

I have no problem running your query in the attached database. I don't need to format the text boxes as date.


There is one thing in the SQL statement that I don't understand though. It is --
totalMonthlyHours are calculated based on the date range entered on the form rather than based on data from the table:-

(DateDiff("d",Forms!frmMain!txtStartDate,Forms!frmMain!txtEndDate)+1)*24 AS totalMonthlyHours

which will return the same number for each group in the query.
.

 

[Brianwarnock]

When I constructed a query identical, as far as I can remember, to Jon's it flagged up the error I mentioned previously, Jon's dosesn't , mind you i was puzzled when mine did.

However as I thought the date string must look like a date

01/006/2007 and 01-06-2007 are ok but 01062007 fails, therefore I would always format and input mask for a date.

Brian

 

[fanfan2]

Thank you guys! Jon - your brilliant! How did you make it work?

Mine still doesn't work somehow. When I run the query, it prompts me for the values, then if I enter, I got the correct results. But if I enter from the form, I got empty rows. What could be wrong? I attached the file.

Yes, the totalMonthlyHours does seem to be a little stupid, but yes, it has the same number for each group in the query.

Thanks so much for your help. I've been frustrated by this for so long...

 

[Jon K]

You haven't named the second text box as txtEndDate. It is Text7.
.

 

[fanfan2]

My God! How should I thank you Jon...My few days' puzzle was solved by your one sentence!

BTW, how did you list the table in the form (under view table)?

 

[fanfan2]

I noticed now the totalMonthlyHours was computed wrong. if I enter startdate=07/01/2007 and enddate=07/16/2007, then the totalMonthlyHours should be 16*24=384. but somehow it was evaluated to 4704. My SQL is: (DateDiff("d",Forms!frmMain!txtStartDate,Forms!frmMain!txtEndDate)+1)*24. I couldn't find any problem, and i got correct answer before with the same statement. Could it be anything wrong? Could I get a hand on this? Thanks soooooo much.

 

[Jon K]

In your database, the query returned 384 totalMonthlyHours
when I entered 07/01/2007 and 07/16/2007 in the text boxes on a system using US date format and
when I entered 01/07/2007 and 16/07/2007 on a system using UK date format.

It returned 4704 totalMonthlyHours only when I entered 07/01/2007 and 07/21/2007 in the text boxes on the UK date-format system.

So you can let the user enter the dates in accordance with the date format the user's system is using.

BTW, how did you list the table in the form (under view table)?

I just selected all the fields from the "availability" table in the Row Source property of the list box and set the Column Heads property to Yes.
.

 

[fanfan2]

Thanks so much Jon. You made life much easier

One more question: is there a way to let user enter a fixed date format and get the correct results? Otherwise I have to ask the user to check their system settings.

 

[Jon K]

See the attached database.

I have added two text boxes on the form in your database for user input in MM/DD/YYYY format and used code in their On Exit events to place the dates in system date format in the original text boxes for use by the query.

You can hide the two original text boxes by changing their Visible property to No.
.

 

[fanfan2]

I couldn't thank you more... May I ask where you are? I appreciate all the help you provided!