MS Query 2000 on dates with parameters

[rasinc]

I am trying to get a query with parameters on a date field to show up properly.

#3/01/2009# And <#3/31/2009#

This above statement works in the date field criteria.

#3/01/2009# And <[Please enter an ending date]

This above statement does not.

Ultimately I want to set it up to do this

=[Please enter a start date] And <=[Please enter an ending date]

Is there any better way to do this or to make the above work properly?

Database is MySQL if it matters.

Any help is appreciated. rasinc

 

[Shane Devenshire]

Hi,

This works for me on the Criteria row in MS Query:

=[Start Date] And <=[End Date]

as does this
Between [Start Date] And [End Date]

Try looking at the SQL statement in MS Query - in my case it is

WHERE (Employees.HireDate Between ? And ?)



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I am trying to get a query with parameters on a date field to show up properly.

#3/01/2009# And <#3/31/2009#

This above statement works in the date field criteria.

#3/01/2009# And <[Please enter an ending date]

This above statement does not.

Ultimately I want to set it up to do this

=[Please enter a start date] And <=[Please enter an ending date]

Is there any better way to do this or to make the above work properly?

Database is MySQL if it matters.

Any help is appreciated. rasinc

 

[rasinc]

I was trying that kind of thing but MySQL doesn't seem to like it. The SQL
statement does have the [Start Date] in the statement instead of ?. I am
sure this worked fine on an Access database I worked on before but it might
be the change in database that makes it not work now. I'll keep hunting.

Thanks for responding.

Hi,

This works for me on the Criteria row in MS Query:

=[Start Date] And <=[End Date]

as does this
Between [Start Date] And [End Date]

Try looking at the SQL statement in MS Query - in my case it is

WHERE (Employees.HireDate Between ? And ?)



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I am trying to get a query with parameters on a date field to show up properly.

#3/01/2009# And <#3/31/2009#

This above statement works in the date field criteria.

#3/01/2009# And <[Please enter an ending date]

This above statement does not.

Ultimately I want to set it up to do this

=[Please enter a start date] And <=[Please enter an ending date]

Is there any better way to do this or to make the above work properly?

Database is MySQL if it matters.

Any help is appreciated. rasinc

 

[rasinc]

Hi Shane,
After some playing around I was able to get rid of the syntax error message
by removing the field =sum(amount) but the query returned no data at all. So
there is something to do with the sum function involved here.

Hi,

This works for me on the Criteria row in MS Query:

=[Start Date] And <=[End Date]

as does this
Between [Start Date] And [End Date]

Try looking at the SQL statement in MS Query - in my case it is

WHERE (Employees.HireDate Between ? And ?)



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I am trying to get a query with parameters on a date field to show up properly.

#3/01/2009# And <#3/31/2009#

This above statement works in the date field criteria.

#3/01/2009# And <[Please enter an ending date]

This above statement does not.

Ultimately I want to set it up to do this

=[Please enter a start date] And <=[Please enter an ending date]

Is there any better way to do this or to make the above work properly?

Database is MySQL if it matters.

Any help is appreciated. rasinc

 

[rasinc]

Figured out the problem. Extra date field was causing the problem when using
a Sum formula. Then the date format in MySQL had to be yyyy/MM/dd. Then it
worked.

Thanks

Hi,

This works for me on the Criteria row in MS Query:

=[Start Date] And <=[End Date]

as does this
Between [Start Date] And [End Date]

Try looking at the SQL statement in MS Query - in my case it is

WHERE (Employees.HireDate Between ? And ?)



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I am trying to get a query with parameters on a date field to show up properly.

#3/01/2009# And <#3/31/2009#

This above statement works in the date field criteria.

#3/01/2009# And <[Please enter an ending date]

This above statement does not.

Ultimately I want to set it up to do this

=[Please enter a start date] And <=[Please enter an ending date]

Is there any better way to do this or to make the above work properly?

Database is MySQL if it matters.

Any help is appreciated. rasinc