Date Query - input information twice

[fuglyjowls]

Hi

I am running Access 2003 and have several queries which ask for the date to be input before they run. Many of them ask for a "from" date and a "to" date i.e. information required from 1/1/09 to 7/1/09. Some of these work straight off no problem but one or two of them require the dates to be input twice before they will work. To clarify it asks for "from" date, "to" date then "from" date and "to" date again in that order.

Can anyone tell me why it does this?

Thanks

FJ

 

[raskew]

Hi -

Would you please post the query SQL.

Bob

 

[fuglyjowls]

Sorry - what is the SQL?

 

[namliam]

You have probably not set the parameter...

Find the SQL by going into Design view of a query, now in the menu View > SQL View

Copy paste that here on the forum and it is nice if you can format it a little, but you probably wouldnt know how to if you dont know what SQL is.

 

[fuglyjowls]

OK thanks for your help - sorry I am not a programmer so I need lots of help. The SQL is:

SELECT [Production Order Summary].id, [Production Order Summary].[order no], [Production Order Summary].Surname, [Production Order Summary].colour, [Production Order Summary].Model, [Production Order Summary].ovens, [Production Order Summary].special, [Production Order Summary].[lid type], [Production Order Summary].[TO Door], [Production Order Summary].[BO Door], [Production Order Summary].[CBox Door], [Production Order Summary].[s/s chopping board], [Production Order Summary].[joining strip], [Production Order Summary].[delivery date], [Production Order Summary].[in house completion], [Production Order Summary].[laser/doors ordered], [Production Order Summary].[painted shell ordered]
FROM [Production Order Summary]
WHERE ((([Production Order Summary].[delivery date]) Between [type the beginning date:] And [type the ending date]))
ORDER BY [Production Order Summary].[delivery date] DESC;

 

[namliam]

Unless [Production Order Summary] is another query I see no reason why this would happen

Here is the sql 'formatted' to be more readable... I am sure you will see the (considerable) increase in readability.

SELECT [Production Order Summary].id
,      [Production Order Summary].[order no]
,      [Production Order Summary].Surname
,      [Production Order Summary].colour
,      [Production Order Summary].Model
,      [Production Order Summary].ovens
,      [Production Order Summary].special
,      [Production Order Summary].[lid type]
,      [Production Order Summary].[TO Door]
,      [Production Order Summary].[BO Door]
,      [Production Order Summary].[CBox Door]
,      [Production Order Summary].[s/s chopping board]
,      [Production Order Summary].[joining strip]
,      [Production Order Summary].[delivery date]
,      [Production Order Summary].[in house completion]
,      [Production Order Summary].[laser/doors ordered]
,      [Production Order Summary].[painted shell ordered]
FROM   [Production Order Summary]
WHERE  [Production Order Summary].[delivery date] Between [type the beginning date:] And [type the ending date]
ORDER BY [Production Order Summary].[delivery date] DESC;

 

[fuglyjowls]

Thanks for your advice - I'll know to format it in future.

I think Production Order Summary is another query - does that help?

 

[namliam]

Then that query probably has simular parameters in it, thus access needs to ask you twice as there are two objects (queries) needing them...

If you already have one query using the parameter(s) to limit your end result, no need for 'double work', not for you, not for the database.

Also....
Dont use spaces or special characters in any names, also to differentiate queries from tables adhere to a naming convention. Most commonly tables are prefixed by tbl (ie. tblProductionOrder) while queries are prefixed qry (i.e. qryProductionOrderSummary)
frm froms, rpt reports, mdl Modules (dont use macro's where you can avoid them, that is about 99.99995% of all macro's, anything a Macro can do a bit of code can do better in a module)

 

[fuglyjowls]

Then that query probably has simular parameters in it, thus access needs to ask you twice as there are two objects (queries) needing them...

If you already have one query using the parameter(s) to limit your end result, no need for 'double work', not for you, not for the database.

Also....
Dont use spaces or special characters in any names, also to differentiate queries from tables adhere to a naming convention. Most commonly tables are prefixed by tbl (ie. tblProductionOrder) while queries are prefixed qry (i.e. qryProductionOrderSummary)
frm froms, rpt reports, mdl Modules (dont use macro's where you can avoid them, that is about 99.99995% of all macro's, anything a Macro can do a bit of code can do better in a module)

Aaaagh I find Access so frustrating - I have just looked at the SQL for the query ProductionOrderSummary and it does not have any such parameters regarding dates - when you open it up it just opens and shows all orders. So I am still none the wiser.

Unfortunately I did not create the database so the lack of naming convention is not my fault - if I change the names to add the prefixes will it cause any problems or will everything just adjust itself?

 

[namliam]

Most things SHOULD adjust themselves.... that is MOST.... it is safest not to mess around (to much) with a database you dont know....
Consider the naming thing FYI should you start your own database some day.

OK so if you open that query, no parameters pop up??
And it is still asking for the EXACT same 2 parameters twice?? Strange??
What shows if you right click the query title bar and select "Parameters", note that your query for the first parameter has : while the second doesnt.

 

[MSAccessRookie]

Aaaagh I find Access so frustrating - I have just looked at the SQL for the query ProductionOrderSummary and it does not have any such parameters regarding dates - when you open it up it just opens and shows all orders. So I am still none the wiser.

Unfortunately I did not create the database so the lack of naming convention is not my fault - if I change the names to add the prefixes will it cause any problems or will everything just adjust itself?

I can offer my experience regarding similar problem that I had before. If the description seems similar, feel free to try my resolution.

What happened to me was that a simple Entry Request [Enter Start Date] was being repeated twice when the query was run. There was no reason for this. Even Compacting and Repairing did not work. What I wound up doing was to copy the SQL Code for the query to a NotePad, and then I created a new query using the EXACT SAME SQL CODE.

Of course, I renamed the old one first, and then removed it after the new query was shown to work. Compacting and repairing made the problem go away and it has not returned as of this time.

Note that I never found the cause of the problem, but I suspect that in my case, it was a weakness of some type in MS Access.

 

[fuglyjowls]

MSAccessRookie - thank you thank you thank you - you have sorted my problem out - I copied the SQL and created a new query and it worked fine. It must be a bug of some sort.

Thanks so much and thanks Mailman for all your help too.

I'll be back no doubt

 

[Ceeder]

MSAccessRookie - Thank you, you've solved an issue that's been plaguing me for a while. It doesn't solve the why but at least it's manageable and for that I'm very grateful.

With this said, I noticed I was able to fix the same issue by simply copying the SQL, deleting it, pasting and then saving without creating a new file. Strange...but it works! Thanks again.