• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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