Parameter date Query problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a simple date query where the user has to enter a specific
date to retrieve a set of records. The problem is that the query works
perfectly for a while and then for no apparent reason it requires the user to
fill in the date twice. There is only 1 parameter query in the design grid.
If I delete the parameter query filter and then paste it back in and save the
query works perfectly again. I am using Access 2003. Can you please advise me
as this is an important database but I cannot solve the problem. Many thanks
Alistair
 
I have created a simple date query where the user has to enter a specific
date to retrieve a set of records. The problem is that the query works
perfectly for a while and then for no apparent reason it requires the user to
fill in the date twice. There is only 1 parameter query in the design grid.
If I delete the parameter query filter and then paste it back in and save the
query works perfectly again. I am using Access 2003. Can you please advise me
as this is an important database but I cannot solve the problem. Many thanks
Alistair
Click to expand...

Have you tried Compacting the database? Might the parameter be
referenced somewhere else on a Form or Report?

It might help to post the SQL of the query; maybe there's something in
it that someone will spot as the error.

John W. Vinson[MVP]
 
Hi John, thanks for your time. I have tried compacting the database and it
made no difference This is the SQL for the query
SELECT [TBL-Scheduling].[First Name], [TBL-Scheduling].[Last Name],
[TBL-Scheduling].[Email Address], [TBL-Scheduling].[Training Date],
[TBL-Scheduling].[Training Completed]
FROM [TBL-Scheduling]
WHERE ((([TBL-Scheduling].[Training Date])<=[enter training date]) AND
(([TBL-Scheduling].[Training Completed])=Yes))
ORDER BY [TBL-Scheduling].[Last Name];
A command button on a form operates the query and the query also is used to
design a report. The report always operates perfectly
 
Hi John, thanks for your time. I have tried compacting the database and it
made no difference This is the SQL for the query
SELECT [TBL-Scheduling].[First Name], [TBL-Scheduling].[Last Name],
[TBL-Scheduling].[Email Address], [TBL-Scheduling].[Training Date],
[TBL-Scheduling].[Training Completed]
FROM [TBL-Scheduling]
WHERE ((([TBL-Scheduling].[Training Date])<=[enter training date]) AND
(([TBL-Scheduling].[Training Completed])=Yes))
ORDER BY [TBL-Scheduling].[Last Name];
A command button on a form operates the query and the query also is used to
design a report. The report always operates perfectly
Click to expand...

Could you explain what you mean by "operates the query"? As a rule,
users should never need to see a query datasheet. If the Report is
based on the query, why not just open the Report (in preview mode if
you don't want it printed immediately) from a command button on the
form?

Perhaps you should also post the code in the button. I don't see
anything in the query which would cause a double prompt. However, I
can suggest that - if you're running this from a form - that you can
avoid the prompt altogether, by putting an unbound textbox named
txtEnterDate on the form and replace

[Enter training date]

with

[Forms]![YourFormNameHere]![txtEnterDate]


John W. Vinson[MVP]
 
Hi John, by "operates a query" I mean that the command button allows the data
entry person to enter a specific date that brings up a set of records that
require updating on a daily basis. It is easier in datasheet view due to the
number of records. The report also goes out the night before and just
requires printing. The report operates off the same query and filter yet
presents no problems. The search form I designed has a number of command
buttons geared to different criteria and this is the only one that gives any
problems, unfortunately it is a critical one as the data entry personnel have
no knowledge of MSAccess or how to amend it.
when I look at the code behind the button on the search form this is what I
see. I am not skilled in this area of database design.
Private Sub Scheduler_update_for_trainers_Click()
On Error GoTo Err_Scheduler_update_for_trainers_Click

Dim stDocName As String

stDocName = "QRY-Training by date and time"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Scheduler_update_for_trainers_Click:
Exit Sub

Err_Scheduler_update_for_trainers_Click:
MsgBox Err.Description
Resume Exit_Scheduler_update_for_trainers_Click

End Sub

John Vinson said:
Hi John, thanks for your time. I have tried compacting the database and it
made no difference This is the SQL for the query
SELECT [TBL-Scheduling].[First Name], [TBL-Scheduling].[Last Name],
[TBL-Scheduling].[Email Address], [TBL-Scheduling].[Training Date],
[TBL-Scheduling].[Training Completed]
FROM [TBL-Scheduling]
WHERE ((([TBL-Scheduling].[Training Date])<=[enter training date]) AND
(([TBL-Scheduling].[Training Completed])=Yes))
ORDER BY [TBL-Scheduling].[Last Name];
A command button on a form operates the query and the query also is used to
design a report. The report always operates perfectly
Click to expand...

Could you explain what you mean by "operates the query"? As a rule,
users should never need to see a query datasheet. If the Report is
based on the query, why not just open the Report (in preview mode if
you don't want it printed immediately) from a command button on the
form?

Perhaps you should also post the code in the button. I don't see
anything in the query which would cause a double prompt. However, I
can suggest that - if you're running this from a form - that you can
avoid the prompt altogether, by putting an unbound textbox named
txtEnterDate on the form and replace

[Enter training date]

with

[Forms]![YourFormNameHere]![txtEnterDate]


John W. Vinson[MVP]
Click to expand...
 
Back
Top