Parameter query (97)

Topic

New Reply

Hello all

I have a query running that shows information until a given date.
When putting this in the query as a parameter it works fine, but when using a popup field to enter the date when you run the query, the results are seldom good. Most of the time the result shows information from later dates.

Why?

Reply | Quote

Replies

When passing a date to a query, you have to use the us format mm/dd/yyyy.
Can you post the sql string you are using?

Reply | Quote

Hello Francois,

Thanks for the reaction. We have a dutch version here so the format is dd/mm/yyyy. I tried different formats
Here is the SQL statement:

SELECT CallFormulier.BerekendeDeadline, CallFormulier.DatumIn, Date()-[BerekendeDeadline] AS [Dagen te laat], CallFormulier.Probleembeschrijving, CallFormulier.Callnummer, CallFormulier.OpenCalls, CallFormulier.TijdsduurGeschat, CallFormulier.NG, CallFormulier.GH, CallFormulier.JV, CallFormulier.PS, CallFormulier.Status.Status
FROM CallFormulier
WHERE (((CallFormulier.BerekendeDeadline)<=[Tot en met welke datum?]) AND ((CallFormulier.OpenCalls)=-1) AND ((CallFormulier.GA)=-1))
ORDER BY CallFormulier.BerekendeDeadline;

If I replace [Tot en met welke datum?] with 30-09-2004 the result is up to 30-08-2004, but if I run the query the way it is en type 30-09-2004 in the popup te result shows date up to 14-10-2004

Reply | Quote

I found the solution by adding the following to the SQL

PARAMETERS [Tot en met welke datum?] DateTime;

Thanks anyway for thinking with me Fran

Reply | Quote

When ever prompted for dates, I always like to enter them dd mmm yyyy (ie. 2 sep 2004). This way, there is absolutely no ambiquity in what I intend the date to be (and MS Access handles the convertion correctly). This may help you in the future (although probably not with this particular problem )

Like you Patric, being in New Zealand, our dates are regionally formatted dd/mm/yyyy , but because of the more ‘native’ use of mm/dd/yyyy I have had a great deal of headaches in the past.

Glad you found a solution.

Reply | Quote

When ever prompted for dates, I always like to enter them dd mmm yyyy (ie. 2 sep 2004). This way, there is absolutely no ambiquity in what I intend the date to be (and MS Access handles the convertion correctly). This may help you in the future (although probably not with this particular problem )

Like you Patric, being in New Zealand, our dates are regionally formatted dd/mm/yyyy , but because of the more ‘native’ use of mm/dd/yyyy I have had a great deal of headaches in the past.

Glad you found a solution.

Reply | Quote

I found the solution by adding the following to the SQL

PARAMETERS [Tot en met welke datum?] DateTime;

Thanks anyway for thinking with me Fran

Reply | Quote

Hello Francois,

Thanks for the reaction. We have a dutch version here so the format is dd/mm/yyyy. I tried different formats
Here is the SQL statement:

SELECT CallFormulier.BerekendeDeadline, CallFormulier.DatumIn, Date()-[BerekendeDeadline] AS [Dagen te laat], CallFormulier.Probleembeschrijving, CallFormulier.Callnummer, CallFormulier.OpenCalls, CallFormulier.TijdsduurGeschat, CallFormulier.NG, CallFormulier.GH, CallFormulier.JV, CallFormulier.PS, CallFormulier.Status.Status
FROM CallFormulier
WHERE (((CallFormulier.BerekendeDeadline)<=[Tot en met welke datum?]) AND ((CallFormulier.OpenCalls)=-1) AND ((CallFormulier.GA)=-1))
ORDER BY CallFormulier.BerekendeDeadline;

If I replace [Tot en met welke datum?] with 30-09-2004 the result is up to 30-08-2004, but if I run the query the way it is en type 30-09-2004 in the popup te result shows date up to 14-10-2004

Reply | Quote

When passing a date to a query, you have to use the us format mm/dd/yyyy.
Can you post the sql string you are using?

Reply | Quote