Query Criteria Error:

[msrobotto]

Hi All,

I am using a query to pull records of all files in a given location. The file location is a dropdown list of possible locations: Pending Approval, Pending Correction, Mailed, Paid.

I would like to be able to run a report of all files "Pending Approval". I created the Query with the Criteria [FileLocation]="Pending Approval" (exactly as it appears on the data table). If a file's location has not been updated it works fine, but I've noticed that if a file's location has been changed it will not appear on the report.

Example: The file starts out "Pending Approval' and appears on the report as it should. The approver requests a correction, so I update the location to "Pending Correction", and the file drops off of the report as it should. I resubmit the file for approval once it has been corrected, and update the file's location to "Pending Approval" but after this change the file will not appear on the report.

I have no idea why.

Any thoughts?

Thanks!

~Vicki

[ajalwaysus]

Sounds like you may be running off an old value, or something as simple as a typo. Please post the code you are using, to help understand how you are executing the report.

-AJ

[msrobotto]

Thanks AJ!

The code is being generated by Access 2007:
----------------------------------------------------------------------

SELECT INVOICEDATA.ProjectManager, INVOICEDATA.ToCustSvc, INVOICEDATA.CustomerNumbers, [CUSTOMER NUMBERS AS400].CustomerNameAS400, INVOICEDATA.InvoiceNumber, INVOICEDATA.OrderNumber, INVOICEDATA.InvoiceDate, INVOICEDATA.ItemTotal, INVOICEDATA.FreightTotal, INVOICEDATA.SalesTaxTotal, [ItemTotal]+[FreightTotal]+[SalesTaxTotal] AS InvoiceTotal, INVOICEDATA.FileLocation, DateDiff("d",[INVOICEDATA].[ToCustSvc],Date()) AS DaysPending, INVOICEDATA.Comments
FROM [CUSTOMER NUMBERS AS400] INNER JOIN INVOICEDATA ON [CUSTOMER NUMBERS AS400].CustomerNumberAS400=INVOICEDATA.CustomerNumbers
 WHERE (((INVOICEDATA.FileLocation)="PENDING PROJECT MANAGER APPROVAL"))
ORDER BY INVOICEDATA.ProjectManager;

--------------------------------------------------------------------

The field INVOICEDATA.FileLocation comes from a dropdown list - so it won't let me typo when I enter the information in that field. It only presents a problem if I have changed the value of INVOICEDATA.FileLocation from anything else to "PENDING PROJECT MANAGER APPROVAL". If it starts out as "PENDING PROJECT MANAGER APPROVAL" it works perfectly.

Rassin' Frassin' Computers! ;)

Thanks again!

~Vicki

[ajalwaysus]

OK, just to make sure I understand so far, the code you gave me is the record source of the report?

If that is the case, 2 things I want to run by you:
1. Are you setting the initial value (on creation) of the record using the same combo box as when you change it from one value to another? (Check the bound column)
2. if 1 is a "Yes", then are you updating the same record, or creating a different record as you update the INVOICEDATA.FILELOCATION field.

-AJ

P.S. for future reference, you should try to post code in the

 CODE

window, it looks like a pound sign above when posting. No Worries.

[msrobotto]

I'll figure out that code posting thing next. :)

I always use the same combo box for file location (bound column = 1), and avoid creating new records whenever possible. I don't need a historical list of the file's location, so I just change it right there on the original record.

Thanks!

~Vicki :)

[ajalwaysus]

Ok sounds normal so far. So my next question to you is, could you post the rowsource code for the FileLocation combobox? Because I'm just wondering if you are storing the actual text "PENDING PROJECT MANAGER APPROVAL" in your table or do you use an ID.

Sorry haven't figured it out yet,
-AJ

[msrobotto]

SELECT [InvoiceDataLocations].[ID], [InvoiceDataLocations].[FileLocations] FROM InvoiceDataLocations ORDER BY [FileLocations]; 

It references an ID... but the actual text appears in the INVOICEDATA data table.

Also, if I use the ID number for "PENDING PROJECT MANAGER APPROVAL" (in this case "2") the query comes up blank.

No worries on not solving the problem yet. I've been playing with it for hours now and it's still vexing me. I appreciate you taking the time!

Thanks!

~Vicki :)

[ajalwaysus]

OK I think that is your issue, you bound the combo box to "1" which refers to the ID not the actual text. The reason you are seeing the text in the table is because you probably set it up to be a lookup, and if that is the case, the field will look up the text value and display that, but it will be storing the ID in the table not the text.

So test this code and let me know if your missing record shows up:

SELECT INVOICEDATA.ProjectManager, INVOICEDATA.ToCustSvc, INVOICEDATA.CustomerNumbers, [CUSTOMER NUMBERS AS400].CustomerNameAS400, INVOICEDATA.InvoiceNumber, INVOICEDATA.OrderNumber, INVOICEDATA.InvoiceDate, INVOICEDATA.ItemTotal, INVOICEDATA.FreightTotal, INVOICEDATA.SalesTaxTotal, [ItemTotal]+[FreightTotal]+[SalesTaxTotal] AS InvoiceTotal, INVOICEDATA.FileLocation, DateDiff("d",[INVOICEDATA].[ToCustSvc],Date()) AS DaysPending, INVOICEDATA.Comments
FROM [CUSTOMER NUMBERS AS400] INNER JOIN INVOICEDATA ON [CUSTOMER NUMBERS AS400].CustomerNumberAS400=INVOICEDATA.CustomerNumbers
WHERE (((INVOICEDATA.FileLocation)=2))
ORDER BY INVOICEDATA.ProjectManager;
 

I noticed you placed the number 2 in quotes, which I did not.

-AJ

[NeoPa]

Nice going AJ.

Another point to consider is :
Have you requeried the data in the report?

A Refresh will only update existing records. The report would determine that the record is no longer matching the criteria, thus drop it. A record which wasn't previously in the list would not suddenly show up however, unless Requery (instead) is used.

Numeric literal values in SQL are used without quotes. Strings are enclosed in quotes, and dates are enclosed in hashes (#). See Quotes (') and Double-Quotes (") - Where and When to use them and Literal DateTimes and Their Delimiters (#).

That is to say, if INVOICEDATA.FileLocation is a numeric field, then there should be no quotes, yet if it is a string field, then they are required.

[msrobotto]

Thanks AJ and Neo!

I only used the quotes around the 2 in typing my reply. :)

When I used 2 as my criteria the report had nothing on it at all.

I decided to delete the field from my query and re-add it, and for some reason that worked. I'm not quite sure why, but it all behaves normally now. Go fig.

And yes, the report triggers the query to run automatically, so I always have the most up to date information possible on the report.

Thanks to you both for all of your help!

~Vicki

[NeoPa]

A pleasure to be of help Vicki :)

[ajalwaysus]

Glad to help. =)

-AJ