How to select dates based on an expression

[sorcerer11]

Hi Everybody,

I have database in which I create an expression within a query.
Real Date: IIf([ShiftPat]="Night",[CDate]-1,[CDate])

It supposed to change dates to match a shift pattern, which it does fine. I then create another query based on this, in which I want to select all data between two dates. But the query seems to treat them like text and not dates and brings in some very strange dates.

Is their anyway I can tell access that this is a date and not a text string. I have tried using DateValue but it doesn't seem to make any difference.

Anybody got any ideas.

Thanks

 

[CosmoKramer]

Try changing your expression to this:

Real Date: IIf([ShiftPat]="Night",Date()-1,Date())

Let me know if this helps...

 

[sorcerer11]

Sorry for the confusion, perhaps I should explain the code a little bit better, the CDate isn't actually the current date function of Access, it's the name I called the field in the table. So it's looking up a date and taking one away from it.

 

[CosmoKramer]

CDate also happens to be the name of a function in Access. You might want to change the name of this control.

To subtract one day from a date field, try DateAdd:

DateAdd("d", -1, [CDate])

will subtract one day from CDate so:

IIf([ShiftPat]="Night",DateAdd("d", -1, [CDate]),[CDate])

 

[sorcerer11]

Tried that but no luck, it does the same thing as my first expression, in that it gives me the information I was after but also gives me other dates. For example if I select 14/05/2003 - 17/05/2003, I will get all those dates plus others like 14/03/2003 and 15/03/2003.

Which I believe is because it's seeing the date as a string expression when looking up the information in the criteria.

 

[sorcerer11]

Just as I side note,

I noticed that critera "between #15/05/2003# and #18/05/2003#" works ok, but not "between [Enter first date] and [Enter last date]". Is their away to put the # sign in whilst still allowing the user to input the dates on the query?

 

[CosmoKramer]

OK, maybe there is a conflict with your regional settings. Try enforcing the format with the parameters. Something like this:

Between Format([Enter first date],"dd/mm/yyyy") And Format([Enter last date],"dd/mm/yyyy")