Syntax with DLOOKUP?

[Dizzzy44]

Hi All

I am having an issue with DLOOKUP in some of my code.

In short I have an unbound form that once a save button is clicked the text boxes and combo boxes in the unbound form dump the data into a table.

The problem I am having is that I need to perform a calculation on some of the data using the DLOOKUP function which keeps returning a null value.

This is the code I have scripted so far. The Year field in the table is a number and the state field is text.

Private Sub btnSaveElec_Click()
'Saves data from unbound text boxes in the Electricity tab to table tblElectricityData

    Dim db As Database
    Dim rst As Recordset
    Dim SaveSQL As String
    
    
    newFlag = Me!chkNewElec
    
    If newFlag = True Then
        SaveSQL = "SELECT * FROM tblElectricityData"
    Else
        SaveSQL = "SELECT * FROM tblElectricityData " & _
                  "WHERE ID = " & Me!TxtIDElec
    End If
    
    Set rst = CurrentDb.OpenRecordset(SaveSQL)
    
    If Me!chkNewElec = True Then
        rst.AddNew
    Else
        rst.Edit
    End If
    
        rst!InvoiceNumber = Me!txtInvoiceNoElec
        rst!Site = Me!txtBDElec
        rst!State = Me!txtStateElec
        rst!Year = Me!txtYearElec
        rst!KiloWattHours = Me!txtkWhElec
        rst!BaseBuilding = Me!ChkBBElec
        rst!Share = Me!txtShareElec
                
        rst!Scope2Emissions = Me!txtkWhElec * DLookup("Scope2EmissionFactor1", "tblStdEFElectricity", _
                              "EmissionFactorRegion='" & rst!State & " And " & "Year=" & rst!Year)
    
    rst.Update
    rst.Close
    Set rst = Nothing
    
    Me!chkNewElec = False
    
    Me!frmsubElectricityData.Requery
    Me!frmsubElectricityData.SetFocus
    
    Me!btnAddNewElec.Enabled = True
    Me!btnEditElec.Enabled = True
    Me!btnCancelElec.Enabled = False
    Me!btnSaveElec.Enabled = False

End Sub
[/CODE

I;m not sure what I am suppose to be doing with rst!Year and rst!State. DO they need to be wrapped in  " or ' . ANy guidance around this would be great.

Cheers

 

[gemma-the-husky]

where is the dlookup?

look in your table

does your code add or edit your records. if not the problem is within this block
if they do, then your dlookup syntax is faulty - which isnt reproduced here.

 

[stopher]

rst!Scope2Emissions = Me!txtkWhElec * DLookup("Scope2EmissionFactor1", "tblStdEFElectricity", _
"EmissionFactorRegion='" & rst!State & " And " & "Year=" & rst!Year)

I can only see one single speech mark.

So the expression should be:

rst!Scope2Emissions = Me!txtkWhElec * DLookup("Scope2EmissionFactor1", "tblStdEFElectricity", _
                              "EmissionFactorRegion=[COLOR="Red"]'[/COLOR]" & rst!State & "[COLOR="Red"]'[/COLOR] And " & "Year=" & rst!Year)

hth
Chris

 

[Dizzzy44]

thanks, that solved it

 

[Dizzzy44]

What about this one? both fields are text

    rst!DistanceKm = DLookup("Distance", "tblStdAirportDistances", "OriginCode='" & rst!Origin & "' AND " & _
                     "DestinationCode='" & rst!Destination)

And if you can, please give me a run down on how this is suppose to work ie when to use '

Cheers

 

[Dizzzy44]

ok, so I solved this but I don't understand the use if single quotation marks properly

    rst!DistanceKm = DLookup("Distance", "tblStdAirportDistances", "OriginCode='" & rst!Origin & "' AND " & _
                     "DestinationCode='" & rst!Destination & "'")

 

[Galaxiom]

The single quotes surround values that are text strings.
They are inside the double quotes to have Access see them as literal characters.

 

[stopher]

ok, so I solved this but I don't understand the use if single quotation marks properly

    rst!DistanceKm = DLookup("Distance", "tblStdAirportDistances", "OriginCode='" & rst!Origin & "' AND " & _
                     "DestinationCode='" & rst!Destination & "'")

Basically you are trying to build an expression that will look like this:
OriginCode='some origin' AND DestinationCode='some destination'

We have to use single quotes to identify a string as opposed to numerical. Ordinarily we could use double quotes but this isn't possible because Access would get confused with the double quotes we will use to build up the expression.
Edit: I made the above comment for the sake of simplicity but it is not the full picture. See Glaxiom's comments below.

The red sections we get from a text string whereas the blue sections come from an expression representing a variable. We use double quotations to identify the textual strings. If I split the above expression hopefully you'll see how the single and double quotes are used:

OriginCode=' (needs to be surrounded with double quotes to show it's a string)

some origin (the variable rst!Origin)

' AND DestinationCode=' (needs to be surrounded with double quotes to show it's a string - although your expression is split into two strings)

some destination (the variable rst!Destination)

' (needs to be surrounded with double quotes to show it's a string)

hope that helps make it clearer. Took me ages to get my head round it.

Chris

 

[Galaxiom]

Actually Access can handle nested double quotes. You have to escape them as required with another double quote which leads to a nightmare with up to four double quote characters in a sequence. Consequently many developers get bamboozled by the syntax.

Another way some developers handle nested quotes is by representing them as an ASCII code: Chr(34). Not a fan of it myself.

Nested quotes can be avoided by using numerical datatypes or, where possible, refering control containing the text rather than its value.
So instead of:

"whatever='" & Me!controlname & "'"

Use:

 "whatever = Forms!formname!controlname"

Access knows the control contains a string so the single quotes are not needed. It does require full reference to the contol (Me! does not work) so the disadvantage is that it is not applicable in subforms that are reused in different main forms.

 

[Dizzzy44]

Ok makes sense now

Cheers