Solved: Parameter Query HELP!

[David627]

I have an issue that needs some quick resolving for a user. He had a DB that we were working on developing for the last few weeks and last Wed (12/31) it was working fine. The only thing the user did was delete the fields in the table so he could start fresh in 2009. Now a query won't work... to be more specific a parameter value in a query.

The query works without a parameter value [Enter Report Date] and even works when I add the parameter value at another Date/Time field in the same query, however once [Enter Report Date] is put in the criteria of the ENTRY DATE Field, which is a Date/Time field, no data shows. I have looked at several MS web pages on the syntax of parameters and it is correct and I have done this before, however am not sure why this would happen.

What am I overlooking??? Please help! This user needs to pull just tomorrows info at the COB tomorrow so the quicker I can resolve this the better!

Thanks
David

[CreganTur]

The only thing the user did was delete the fields in the table so he could start fresh in 2009.

Be careful of your terminology here. Do you mean that the User deleted actual Table fields, or did the user delete records in the table?

Is there a date in the records that matches the date being used as a parameter? If no date exists in the table, then the query is working correctly.

If this doesn't help, then please post the SQL... perhaps there is a syntax issue. otherwise, can you post an example database that replicates the exact issue?

I'm GFTD in a few, but someone else may be able to provide an answer for you soon. If not, then I'll look into this first thing in the morning.

[David627]

Sorry - in my haste I mispoke - they deleted records. There are dates that match the value the user is entering when the parameter query is run.

SQL is:
SELECT CHECKS.[Entry Date], CHECKS.CHECK_NO, CHECKS.[Check Date], CHECKS.PAYER, CHECKS.[Permit Fee], CHECKS.Surcharge, CHECKS.[Amendment Fee], CHECKS.[Configuration Fee], CHECKS.[Contiguous County Fee], CHECKS.[Permit Number], CHECKS.[Permit Type], CHECKS.[Permit Class], CHECKS.Rejected, CHECKS.USDOT, ([Permit Fee]+[Amendment Fee]+[Configuration Fee]+[Contiguous County Fee]+[Surcharge]) AS [Check Amount]
FROM CHECKS;

Again, this worked last week...

[OBP]

David, check that the Dates in the Table are not being stored as Date & Time.
If they are they will not Match a Date Parameter Input because of the Time part of the Date.
So instead of using the Date field itself use a formatted version of it for the parameter Column. i.e. a new ColumnHeading of

Realdate:Format([ENTRY DATE],"mm/dd/yyyy")

assuming that you are using the USA date format
Now put your Parameter in that Column's Criteria Row.

It would be better if your Parameter Date was actually Input on a Form because then any number of Queries/Reports can use it for just the 1 entry, instead of having to enter it for each one.

PS it is not a good idea to show your email address on your Posts as you may get "Spammed".

[David627]

>If they are they will not Match a Date Parameter Input because of the Time part of the Date.
Then how come this worked fine last week in the multiple tests I did?

>Realdate:Format([ENTRY DATE],"mm/dd/yyyy") Now put your Parameter in that Column's Criteria Row.

This didn't work

[OBP]

Can you post a zipped copy of the Table & Query for us to look at?
It only needs the Dates in the table.

[David627]

Here is file - you are looking at SUM query.

Thsnks
David

[CreganTur]

Tony is right- the problem is caused by the fact that your [Entry Date] field (BTW, it's bad practice to have spaces in field names. If you want to separate words, use an underscore character. EntryDate and Entry_Date conform to best practice) contains both date and time.

Open up your table and overwrite some of the date & time values to just a date, then run your parameter query- it will work.

How are values entered into your EntryDate field? Can you adjust your process so that the date only is entered into this field? I can't tell how the values are getting into the table from the DB you posted.

[David627]

So why did this work before??

David (puzzled!)

[CreganTur]

So why did this work before??

Possibly the old records were correct- date without the time.

How are values entered into your EntryDate field? Can you adjust your process so that the date only is entered into this field? I can't tell how the values are getting into the table from the DB you posted.

[David627]

They are being entered through the form

[David627]

Found out the answer - the table was being populated by the form which had the default for the entry date field as =now() which added in the time along with teh date. I changed it to =Date() and it works.

Thanks to all
David

BTW: I am well aware of the correct naming conventions, including using appropriate prefixes, however this is not my database and I am not going to change items beyond what was asked, however I did suggest using correct naming to the user.