syntax problems

jlabre01

Registered User.
Local time
Today, 12:39
Joined
May 26, 2008
Messages
62
Hi,
I've created a form that uses a drop down menu for the user to select the company they're looking for and a secondary drop down menu that shows the invoice reference number for the company's invoices. The problem is with the secondary drop down menu, the statement I used to define what I wanted it to show is as follows:
SELECT Projects.InvoiceNumber FROM Projects WHERE Projects.CompanyID = Forms!Invoice!CompanyID;

For some reason it displays nothing, when I remove the where statement it shows all invoice numbers, please help.

jlabre01
 
If this is in the rowsource property then refer to it like:

SELECT Projects.InvoiceNumber FROM Projects WHERE Projects.CompanyID = [Forms]![Invoice]![CompanyID];
 
Hi,
I tried to add the square brackets around the names at the end as suggested but when i save it the square brackets are gone and it doesn't work, what should I do?

jlabre01
 
Are you sure your form name is Invoice and your CompanyID is a number and not text?
 
You would have to requery the second combo after making a selection in the first. Have you done that?
 
Hi,
ok I think I figured it out, in the projects table companyID is names, but in the companies table it's numbers. So how would i use the where statement to do the same thing as before?if i have to get the invoice number to output from the projects table and the company id from the companies table?

jlabre01
 
You should redesign the table that is storing it as a foreign key to be a Number (Long Integer). Don't try to Band-aid fix it. Fix the actual design flaw.
 
Hi,
I just looked in the design view at the projects table, companyID is defined as a number (long integer) but somehow in the table there are just names. not sure how to fix that.
 
Ah, you would appear to be suffering from the ill-effects of LOOKUP FIELDS AT TABLE LEVEL. You want to remove them.


To remove the lookup field:

lookup01.png


lookup02.png
 

Users who are viewing this thread

Back
Top Bottom