between query

[jerry28ph]

Good Morning Everyone,

I'm getting stuck on between date query, it always shows null value when I run the query,

SELECT [Smdr(backup1-5-2009)].Party1Name, [Smdr(backup1-5-2009)].[Call start], [Smdr(backup1-5-2009)].[Call duration]
FROM [Smdr(backup1-5-2009)]
WHERE ((([Smdr(backup1-5-2009)].[Call start]) Between [forms]![frmPCLog]![text49] And [forms]![frmPCLog]![text51]));


Is there somthing wrong with this code?
Please need someone's help.

Thanks.

Regards,
Jerry

 

[pbaldy]

It looks correct syntax-wise (other than the inadvisable spaces and symbols). What is the data type of the [Call start] field, and what does the data look like? What is being entered into the form controls?

 

[Brianwarnock]

Nothing that I can see but I seem to remember a thread recently where somebody was having problems that resolved as being due to - in the table name, it was changed to _ and the query worked. I'll see if i can find it.

Brian

 

[jerry28ph]

Call start is Date/Time [data types] - i dont have access to my company's Phone Server that's why I dont have authorization to change the settings of the field. I just imported the CSV files from server.

in table Call start field has value of "1/5/2009 7:02:00 AM"
but on the forms, the value is just short date like 1/5/2009.

do you have any idea, i really stuck on this since this morning.

Thanks for your time.

Best regards,
Jerry

It looks correct syntax-wise (other than the inadvisable spaces and symbols). What is the data type of the [Call start] field, and what does the data look like? What is being entered into the form controls?

 

[jerry28ph]

THanks Brian, Im looking forward to it.
Best regards

Nothing that I can see but I seem to remember a thread recently where somebody was having problems that resolved as being due to - in the table name, it was changed to _ and the query worked. I'll see if i can find it.

Brian

 

[Brianwarnock]

Well the date field from the form will default to a time of 00:00:00 thus will be after the call time quoted and thus that call wont be included. for the end date/time you should add 23:59:59 to capture the data on that day. you may get away with the slightly less accurate method of adding 1 day.

Brian

 

[Brianwarnock]

This was the thread that I was thinking of but the syntax and symptoms are different, still as paul pointed out you cannot be too careful.

Brian

 

[jerry28ph]

since date/time is present on the field value like "1/5/2009 00:07:35 AM", can I just change the date format to "short date" to eliminate the time, coz I dont need the time anyways. I just after the date, after i run the query i want to view the filtered date based on value that I keyed on the form.

Well the date field from the form will default to a time of 00:00:00 thus will be after the call time quoted and thus that call wont be included. for the end date/time you should add 23:59:59 to capture the data on that day. you may get away with the slightly less accurate method of adding 1 day.

Brian

 

[pbaldy]

I agree with Brian about the date/time issue. I suspect if you search on a multiple date range, you'll get records, but not the last day's. If you search on a single date, you won't get anything (unless a record has a time of 00:00:00).

 

[Brianwarnock]

since date/time is present on the field value like "1/5/2009 00:07:35 AM", can I just change the date format to "short date" to eliminate the time, coz I dont need the time anyways. I just after the date, after i run the query i want to view the filtered date based on value that I keyed on the form.

No changing the format ie the presentation does not alter the fact that the field contains the time element. add +#23:59:59# after the [text51] in your SQL, I take it thus us the enddate.

Brian