convert string to date 1

[mflancour]

I have a numeric month, a numeric year and I am concatinating them together to produce a date value. I need to filter this value using a between date and date. How can I do this? My first guess is to convert the string to a date value, but havn't had any luck yet. Tried Format(MyTable.MyDate,"mm/dd/yyyy") but didn't do a darn thing.

 

[rjoubert]

Try the CDate Function...CDate(MyTable.MyDate)

 

[mflancour]

is that a function for access 2003 queries? it dosn't seem to recognize it

 

[rjoubert]

Yes, it's in Access 2003. How are you calling it, via a Query?

 

[mflancour]

yes

 

[mflancour]

got the function to work, but it still sees teh value as a string even with the function.

 

[rjoubert]

I forgot that you only have a month and a year. You can try putting in "01" for the day to make it a complete date.

 

[mflancour]

ya, I have it that way now concatinating cdate(month & "/01/" & year)... and it still reads as a string

 

[lespaul]

what's the entire SQL of the query?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[mflancour]

SELECT CDate([Month] & "/01/" & [year]) AS Expr3
FROM dbo_SalesAnalysis_T
WHERE (((CDate([Month] & "/01/" & [year]))>1/1/2006));

 

[rjoubert]

Try putting the "#" symbol around the date parameter...

> #1/1/2006#

 

[mflancour]

haha, sweet. Looks like that was the final trick. strange thought that should be automatic. Thanks a lot.