Solved

Control source syntax

Posted on 2015-01-03
12
113 Views
Last Modified: 2015-01-03
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
Comment
Question by:SteveL13
  • 6
  • 5
12 Comments
 

Author Comment

by:SteveL13
ID: 40529248
Part_No is a text field.
0
 
LVL 39

Expert Comment

by:als315
ID: 40529252
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529254
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:SteveL13
ID: 40529260
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529261
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529268
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
 

Author Comment

by:SteveL13
ID: 40529271
Getting "The expression you entered has an invalid string" with the last suggestion.
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40529273
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
 

Author Comment

by:SteveL13
ID: 40529276
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529277
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529279
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
 

Author Comment

by:SteveL13
ID: 40529283
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question