• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • Last Modified:

Control source syntax

I have the following code in a control source of a field on a form but it isn't correct.  Can someone help me with the syntax?

=Nz([qryQtyDiesOrdered.SumOfQTY_ORDERED] WHERE [Part_No] = Forms!frmParts.txtPart_No -[qryQtyDiesOrderedAndReceived.SumOfQTY_ORDERED] WHERE [Part_No] = Forms!frmParts.txtPart_No  ,0)
0
SteveL13
Asked:
SteveL13
  • 6
  • 5
1 Solution
 
SteveL13Author Commented:
Part_No is a text field.
0
 
als315Commented:
Can you explain idea? What is expected result?
If you like to get result from query, use dlookup or subform with one field only
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Test to see it criteria shows up and what is it variable type:

Step 1:
= "Show me value: " & Forms!frmParts.txtPart_No

Step 2: Find its variable type (after step 1 above shows the value correctly)
= "Show me variable type: " & VarType(Forms!frmParts.txtPart_No)

If variable type = 8 then Forms!frmParts.txtPart_No is text, it 2 then long, report back your finding.

Mike
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
SteveL13Author Commented:
Ok.  Am trying a different approach using on current event of the form.  What is wrong with this...?

me.txtQtyOnOrder = Nz(DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] in ('I','O','A')"),0)+Nz((DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] ='ORD'"),0)-Nz(DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [DATE_RECD] Is Not Null"),0))
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
If vartype is 8:

= IIF(Nz(DLooup("SumOfQTY_ORDERED", "qryQtyDiesOrdered", "[Part_No] = '" & Forms!frmParts.txtPart_No & "'"),0),  DLooup("SumOfQTY_ORDERED", "qryQtyDiesOrdered", "[Part_No] = '" & Forms!frmParts.txtPart_No & "'"), (Nz(Dlookup( "SumOfQTY_ORDERED", "qryQtyDiesOrderedAndReceived", "Part_No" = '" & Forms!frmParts.txtPart_No & "'),0))
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
code for If vartype is 8 is revised:

If vartype is not 8:

= IIF(Nz(DLooup("SumOfQTY_ORDERED", "qryQtyDiesOrdered", "[Part_No] = " & Forms!frmParts.txtPart_No ),0),  DLooup("SumOfQTY_ORDERED", "qryQtyDiesOrdered", "[Part_No] = " & Forms!frmParts.txtPart_No ), (Nz(Dlookup( "SumOfQTY_ORDERED", "qryQtyDiesOrderedAndReceived", "Part_No" = " & Forms!frmParts.txtPart_No ),0))
0
 
SteveL13Author Commented:
Getting "The expression you entered has an invalid string" with the last suggestion.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Last version with vartype = 8

= IIF(Nz(DLooup("SumOfQTY_ORDERED", "qryQtyDiesOrdered", "[Part_No] = '" & Forms!frmParts.txtPart_No & "'" ),0),  DLooup("SumOfQTY_ORDERED", "qryQtyDiesOrdered", "[Part_No] = '" & Forms!frmParts.txtPart_No  & "'" ), (Nz(Dlookup( "SumOfQTY_ORDERED", "qryQtyDiesOrderedAndReceived", "Part_No" = '" & Forms!frmParts.txtPart_No & "'" ),0))
0
 
SteveL13Author Commented:
Now still trying to break apart the oncurrent event.  What is wrong with this code?

me.txtQtyOnOrder = Nz((DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] ='ORD'"),0)
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Was vartype 8? Did you have time to test it or you already know it is string?

Did you try to see if the value of

Step 1:
= "Show me value: " & Forms!frmParts.txtPart_No

shows up?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
SteveL13,

You are loosing me. Is this a new question? If so, wait to answer current one and keep your new question for another thread.

Please do step 1 and 2 above to continue.

Mike
0
 
SteveL13Author Commented:
This worked:  All I had to do was correct spelling of DLookup

= IIF(Nz(DLooup("SumOfQTY_ORDERED", "qryQtyDiesOrdered", "[Part_No] = '" & Forms!frmParts.txtPart_No & "'" ),0),  DLooup("SumOfQTY_ORDERED", "qryQtyDiesOrdered", "[Part_No] = '" & Forms!frmParts.txtPart_No  & "'" ), (Nz(Dlookup( "SumOfQTY_ORDERED", "qryQtyDiesOrderedAndReceived", "Part_No" = '" & Forms!frmParts.txtPart_No & "'" ),0))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now