Query dates saved as text... (1 Viewer)

[tangcla]

In a continuation of this thread here, I have a table which has a date/time field which contains a timestamp (which I can omit, if someone can tell me how to do so with a linked table...)

I want to do date-based queries with this, i.e. between Now() and Now()-28, but because it's a text field, this does not work.

Is there a way to treat the column as a date field for the purpose of the query?

 

[giovaanni]

Just do a CDATE(yourdatefield) and then filter this by your range.

 

[tangcla]

Just do a CDATE(yourdatefield) and then filter this by your range.

Do I do this inline or does it have to be run to convert a table column first?

 

[giovaanni]

Just type what I mentioned into a field in a query that has your date field.

 

[giovaanni]

It doesn't have to be converted first, you can have the expression field on it's own and then filter it by typing into the criteria between now() -25 and now() for the field.

 

[tangcla]

It doesn't have to be converted first, you can have the expression field on it's own and then filter it by typing into the criteria between now() -25 and now() for the field.

It won't work that way, it will treat it as between Now() and Now()-"28" automatically.

 

[tangcla]

Just type what I mentioned into a field in a query that has your date field.

I tried it and if I try to give criteria, it tells me "invalid use of null".

 

[giovaanni]

That means you have nulls in your date text column.

In that case do this CDATE(NZ(yourdate,0)) for the expression field in the query.

 

[tangcla]

That's the strange thing - there are no blanks in the dataset, I tried.

As another approach, I used the DateValue() function and that allowed me to convert the text to date - however I think because Access thinks that the field is text, it won't allow me to use date functions to specify query criteria.

 

[tangcla]

That means you have nulls in your date text column.

In that case do this CDATE(NZ(yourdate,0)) for the expression field in the query.

This worked, win thanks. +rep.