DATE comparison in VB6 and ACESS 2000

[bubu]

My Table in Acess 2000 have a field called: expiration_date that is of type Memo. I save date values there in this format dd/mm/yyyy

In my visual basic program i have a field where i write a date in the same format (dd/mm/yyyy)

I need to get all records where expiration_date <= vb_field_date


Got it? how i do it! 've been trying for the whole day in many formats!!! I even tried concatenating the date parts to form a number and ocmpare it as a number like this:



SELECT * FROM mytable WHERE
CDBL(FORMAT(YEAR(expiration_date),'0000') & FORMAT(MONTH(expiration_date),'00') & FORMAT(DAY(expiration_date),'00'))
<=
CDBL('" & Format(Text_Year, "0000") & Format(Text_Month, "00") & Format(Text_Day), "00") & "')"



this should work since it convert dates into equivalent numbers and compare em but does not work :cry:


Plz help me!!!

Ps. I need the expiration_date field in Memo format.

[exterminator]

Memo type column for storing date????? :eek: That's insane.. you should use either Date/Time column or if not then at most Text... nothing else...

The various date functions you have used would only work for a valid date - the format must be what is defined in your computer's regional settings (cp). That is why it's best to keep it in a datetime typed column.

Do this - and please post the exact error message that you get. They are helpful once in a while.. you shouldn't ignore them. Regards.

[bubu]

There is no error message since there is no error... It only doesn't work as i want.

All I wanted is to know if there is an SQL function to convert a date value in memo (string) format to date format.

Thanks for atention...

[exterminator]

Does putting # around the text date help? Use DateDiff using strings surrounded by a #. If this doesn't work. Take out the day, month and year from both the table retrieved value and the date that you have and compare them seperately to finally decide upon which one is greater.. or whatever..

There is no function as such to convert a text to date ... Regards.

[ITGURU]

Dear bubu,

I am totally agree with whatever exterminator telling i.e. why you want to store date type value in Memo field? It will be better if you store your date type value in some DateTime field or Text type field.

In regards to your problem, you have written that you always stored date in Memo field as dd/mm/yyyy format, if this is correct then your YEAR, MONTH and DAY function of MSACCESS never work because these function always expect date in MM/DD/YYYY format.

But I have solution for your problem which is as follows:
1. Convert your SQL query with my SQL Query (please check the syntax with MSACCESS because me does not have MSACCESS on my machine):

SELECT * FROM mytable WHERE
CDBL(RIGHT(('0000' & MID(TRIM(expiration_date), 7, 4)), 4) & RIGHT(('00' & MID(TRIM(expiration_date), 4, 2)), 2) & RIGHT(('00' & MID(TRIM(expiration_date), 1, 2)), 2))
<=
CDBL(RIGHT(('0000' & MID(TRIM(Text_Year), 7, 4)), 4) & RIGHT(('00' & MID(TRIM(Text_Month), 4, 2)), 2) & RIGHT(('00' & MID(TRIM(Text_Day), 1, 2)), 2))

Hope this will solve your problem.