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
11 2099
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
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.Fil eLocation 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.Fil eLocation 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
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.FIL ELOCATION field.
-AJ
P.S. for future reference, you should try to post code in the
window, it looks like a pound sign above when posting. No Worries.
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 :)
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
- 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 :)
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 32,579
Recognized Expert Moderator MVP
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.Fil eLocation is a numeric field, then there should be no quotes, yet if it is a string field, then they are required.
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: news.paradise.net.nz |
last post by:
I have been developing access databases for over 5 years.
I have a large database and I have struck this problem with it before
but can find nothing in help or online. Access 2000
I have a query that will run fine without any criteria but as soon as I
add any criteria it gives a "Data type mismatch" error.
As soon as I remove any criteria it runs perfectly.
I know this query is based on another query but I have other processes
based on...
|
by: paii, Ron |
last post by:
I am trying to use the following "Elookup" function on a query that gets 2
parameters from an open form.
?eLookup("", "qryWOLPCMatEst")
Returns the following error.
Too few parameters. Expected 2. 48 eLookup Error 3061
Error 3061
|
by: SuffrinMick |
last post by:
Hello - I'm a newbie to coding!
I'm working on an access 2000 database which has three tables:
tblContacts - A list of customer contacts.
tblOrgTypes - A list of organisational types.
tblPositions - A list of job descriptions (Contacts can hold more than
one position)
I want to use a multi-select list box (Containing alphabetical list of
positions) to run a query. HELP!
|
by: starace |
last post by:
I have designed a form that has 5 different list boxes where the
selections within each are used as criteria in building a dynamic
query. Some boxes are set for multiple selections but these list boxes
do not necessarily need to have a selection made to be used in the
dynamic query.
In essence the form can have selections made in all or none of its list
boxes to form the dynamic query
I am looking to get some feedback in reference to...
|
by: MLH |
last post by:
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...
Between #12/1/2004# And #12/31/2004# - or -
>= #6/10/2005# - or -
Between #6/1/2005# And #6/30/2005#
I want to use them as criteria for the query.
| |
by: MLH |
last post by:
Am repeating question with different subject heading, perhaps
stating more clearly my problem...
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...
Between #12/1/2004# And #12/31/2004# - or -
>= #6/10/2005# - or -
|
by: martlaco1 |
last post by:
Trying to fix a query that (I thought) had worked once upon a time, and
I keep getting a Data Type Mismatch error whenever I enter any criteria
for an expression using a Mid function. Without the criteria, the Mid
function returns the values when I run the query. So if one of the
values is a "t" (no quotes), can I not ask to isolate that record by
putting "t" as a criteria? Nope - error, error.
If I put it within the expression itself...
|
by: ecoulson123 |
last post by:
I am using Access 2000. I am trying to summarize numeric data from a
large database. The problem is that I need the summarization functions
to ignore "junk" data, defined in a couple ways.
Assume an excerpt of the database table looks like this:
Lot SerialNumber Status Error
101 001 Good .3
002 Good .2
003 Mach Fail .4
|
by: aaronrm |
last post by:
I have a real simple cross-tab query that I am trying to sum on as the
action but I am getting the "data type mismatch criteria expression"
error. About three queries up the food chain from this cross-tab
query I am using a simple query with no grouping where I am filtering
some data out in the criteria line.
I have been out of access for a couple years but I remember in the
past I had a solution for this but I can't remember. Any help...
|
by: sharsy |
last post by:
Hello guys,
I would like some help in generating query criteria that will identify
credit cards that have expired on an access database.
The specific Field is formatted with a Data Type of
'Text' and has an input mask of '00/00', so all expiry dates are set
out for example as 10/13 (which represents October 2013).
I have hada brief go at trying to work it out, but I was unable to
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |