Date query/Strip off time

[TheB]

Hi,

I am dynamically building a query based on user choices. Two choices within the query are to pick a beginning date and an ending date. I am using the Microsoft date/time picker for both. When I debug the form to see the query it is showing time as well as date. How can I strip off time so I just have date in the query.

sWhere = sWhere & " AND [AD_SALES_DATE] BETWEEN " & BegSalesDate & " AND " EndSalesDate.

BegSalesDate and EndSalesDate have date as 7/14/2003 10:37:51 pm. I want to strip off the time. Is there a function to do this?

Thanks in advance.

...TheB

 

[pono1]

The Integer function will do it since days are stored as integers and time as a fraction of a day...

	BegSalesDate = Int(BegSalesDate)
	EndSalesDate = Int(EndSalesDate)

Regards,
Tim

 

[TheB]

Tim,

Thanks for the reply. Took your advice and it worked. It did strip off the time. My next problem is that the AD_SALES_DATE is defined as Short Date. with the Int function I have converted the BegSalesDate/EndSalesDate to Integer. Now I am comparing type date to type integer. How would I convert the integer date back to date type?

...Bruce

 

[pono1]

Bruce,

       BegSalesDate = Cdate(int(BegSalesDate))

Regards,
Tim

 

[TheB]

Tim,

Once again thanks for your help. However. my query does not seem to be working. Let me explain further...

This is my syntax coded in VB to generate my where clause for the report I am working on.

sWhere = sWhere & " AND ([AD_SALES_DATE] BETWEEN " & CDate(Int(BegSalesDate)) & " AND " & CDate(Int(EndSalesDate)) & ")"

What I am wondering is if I need to add some kind of ToDate function to the BegSalesdate and EndSalesDate in order for the SQL to work. I am familiar with ORACLE and I would have to add a ToDate function to date fields I would be using to cpmpare with date fields in a database.

Can you help?

Thanks for your help so far.

..Bruce

 

[pono1]

Bruce,

I can only offer a guess: it's probably a syntax error. Try to simplify your code until it works and then build it back up one piece at a time. Also, perhaps first thing, search on BETWEEN and DATE and SYNTAX and DYNAMIC in the Query section of the forum for many examples using dates in SQL strings.

You can also build "practice" queries with the Access Query grid and then click View-SQL on the pulldown menu to see the Access implementation of SQL strings...

If you're still stuck after all that, post back under a new topic with more of your code and a specific explanation of what happens (or doesn't happen) to indicate that it is failing to do what you want it to do.

Regards,
Tim

 

[TheB]

Tim,

Thanks again for the help but I was able to discover what I needed. You need to surround the date with # signs to tell access the literal is to be treated as a date.

Again thanks for all your help.

...Bruce

 

[Mile-O]

How can I strip off time so I just have date in the query.

Is there a function to do this?

Even simpler: The DateValue() function.

 

[TheB]

Thanks Mile!!! Worked great!!!