DATE PARAMETER QUESTION

[dridgeway]

I have:

Field: DATE (Text, not date)

Table: DPADV

Between [Start] and [End]

I cannot get it to query the data range I enter, it pulls everything. I'm linking to a table, not importing

 

[mpastore]

What do the text dates look like? Can you give some examples?

Mike Pastore

Hats off to (Roy) Harper

 

[dridgeway]

example is: 06/03/03

 

[mpastore]

Cast the text fields as date:

Between cdate([Start]) and cdate([End])

Mike Pastore

Hats off to (Roy) Harper

 

[dridgeway]

mpastore,

I'm so much closer now, thank you, however, when I enter for instance 02/02/03 - 04/02/03 i get just those months and days, but it returns all years....00, 01, 02, 03....
I've also tried inputting 2003 to no avail.

 

[mpastore]

Can you post query SQL here for me to look at?

Mike Pastore

Hats off to (Roy) Harper

 

[dridgeway]

SELECT DPADV.PICKUP, DPADV.DATE
FROM DPADV
WHERE (((DPADV.DATE) Between CDate([Start]) And CDate([End])));

 

[mpastore]

Try

SELECT DPADV.PICKUP, DPADV.DATE
FROM DPADV
WHERE ((CDate(DPADV.DATE) Between CDate([Start]) And CDate([End])));

Mike Pastore

Hats off to (Roy) Harper

 

[dridgeway]

Darn, that gives me "This expression is typed incorrectly, or is too complex to be evaluated."

I copied and pasted from your reply and double checked the syntax and it appears good.

 

[mpastore]

Try

SELECT DPADV.PICKUP, DPADV.DATE
FROM DPADV
WHERE CDate([DPADV].[DATE]) Between CDate([Start]) And CDate([End]);

Also, you may want to change thename of your field called 'DATE' to something else. 'DATE' isa reserved word in MS Access and may confuse the compiler.

Mike Pastore

Hats off to (Roy) Harper

 

[dridgeway]

mpastore,

here's what i ended up doing and it works fine.

PARAMETERS DateTime, [E] DateTime;
SELECT DPADV.PICKUP, IIf([DATE]=' / / ',CDate('01/01/1980'),CDate([DATE])) AS AdvDate
FROM DPADV
WHERE (((IIf([DATE]=' / / ',CDate('01/01/1980'),CDate([DATE]))) Between And [E]))
ORDER BY IIf([DATE]=' / / ',CDate('01/01/1980'),CDate([DATE]));

The application that creates this table will populate date with ' / / ' if nothing's entered. THANKS for ALL the help.