Using SQL to query a query

[diddydustin]

Hi,

I have a query, EMR_TotalSheets, that selects job numbers and the max number of sheets (this is a production facility) for each job.

I want to query this query for the job number, but I having trouble doing so.

I'm trying this:

SELECT [EMR_TotalSheets].[Max of Count_Stop] FROM EMR_TotalSheets WHERE [EMR_TotalSheets].Job_No = Forms!EMR_Sheeter!Job_No.Value

The result is a #NAME? in the textbox I am trying to to use.

Note, the Forms!EMR_Sheeter!Job_No.Value is a value that is coming from the current form with the job number they entered.

Can anyone see what I am doing wrong?

Thanks
Dustin

 

[TonyJollans]

Hi diddydustin,

I think you just want the control name from your form, Forms!EMR_Sheeter!Job_No without the .Value property on the end.

Enjoy,
Tony

 

[kewo99]

Is Job_No a number or text data type?

Ken

 

[scriverb]

From your posting I am reading between the lines that you are trying put the SQL posted in the Control Source of a text box. The result is a #Name? being show. You can't do what you are trying to do. You can however use the DLookUp function and extract the field value from the SQL.

Save your query as qryMaxCountStop.

The following expression can be copied and pasted into the Control Source of a text box.
DLookUp("[Max of Count_Stop]", "qryMaxCountStop&quot


Post back if this is not really what the problem is.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[GoDawgs]

Just use a lookup:

Dlookup("[Max of Count_Stop]","EMR_TotalSheets","[Job_No] = " & Forms!EMR_Sheeter!Job_No.Value)

Hope that helps.

Kevin

 

[diddydustin]

Hey, that worked. Now my next task with the Dlookup function is parsing multiple queries; how can I match job numbers, dates, and shifts? And if it doesn't exist, default to 0?

Thanks!
Dustin

 

[scriverb]

Create a saved query with the appropriate joins to select the records and match the data. Then use that saved query in your DLookup function as the Source.

=IIF(DCount("*","qryYourSavedQuery&quot= 0, 0,DLookup("[fieldname], "qryYourSavedQuery&quot)

Post back with questions or comments.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[diddydustin]

Hey guys,

Everything is working great. I decided to great a unique ID for each shift, so now I'm just building this ID and then querying the query for that. My problem is building the string. Maybe you all can help:

= DLookUp("[Max of Count_Stop]","EMR_TotalSheetsByJobID","[Job_ID] = " & [Forms]![EMR_Sheeter]![Job_No].[Value] & Date$() & [Forms]![EMR_Sheeter]![Shift].[Value] & "'&quot

A Job ID should look like this:

123121001 + 071303 + 2
Job No + Date + Shift
1231210010713032

I'm afraid my Date$() function isn't returning the date in the format "mm/dd/yy" and I can't find any information on formatting within the expression builder or whatever. Does anyone know how to do this?

Pretty simple question... thanks for any response.

Dustin

 

[scriverb]

Try this:
Format(Date$(), "mm/dd/yyyy&quot

Let me know if that works for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[diddydustin]

Works great! Thanks so much!

Dustin

 

[scriverb]

Glad to be of assistance.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???