Using DLookup to enter data in a field

[Guest]

The code below produces an error message #3464,"Data Type Mismatch in
Criteria Expression" and I would appreciate knowing how to correct the
criteria expression. The code stops running on the next to last line, and the
Criteria shown when the pointer is passed over MyCriteria is: "[CabinNum] =
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Douglas J. Steele]

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:

 

[Guest]

Thanks Doug,
It is text and I would appeciate it if you would tell me how to modify the
"MyCriteria = " line to put them there.

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The code below produces an error message #3464,"Data Type Mismatch in
Criteria Expression" and I would appreciate knowing how to correct the
criteria expression. The code stops running on the next to last line, and
the
Criteria shown when the pointer is passed over MyCriteria is: "[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Douglas J. Steele]

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
It is text and I would appeciate it if you would tell me how to modify the
"MyCriteria = " line to put them there.

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The code below produces an error message #3464,"Data Type Mismatch in
Criteria Expression" and I would appreciate knowing how to correct the
criteria expression. The code stops running on the next to last line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[John Vinson]

The code below produces an error message #3464,"Data Type Mismatch in
Criteria Expression" and I would appreciate knowing how to correct the
criteria expression. The code stops running on the next to last line, and the
Criteria shown when the pointer is passed over MyCriteria is: "[CabinNum] =
208", for examlple.

I'm guessing that the CabinNum field is of Text datatype rather than
Number (I'd certainly recommend that choice anyway); if so you need
quotemarks to delimit it:

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = '" & MyCabNum & "'"


John W. Vinson[MVP]

 

[Guest]

Please bear with me, but now I get an error message "Invalid use of null'
There are no nulls in MyDomain
Thank you for your patience

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
It is text and I would appeciate it if you would tell me how to modify the
"MyCriteria = " line to put them there.

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code below produces an error message #3464,"Data Type Mismatch in
Criteria Expression" and I would appreciate knowing how to correct the
criteria expression. The code stops running on the next to last line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Douglas J. Steele]

If DLookup doesn't find a matching entry, it will return Null. I suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Please bear with me, but now I get an error message "Invalid use of null'
There are no nulls in MyDomain
Thank you for your patience

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
It is text and I would appeciate it if you would tell me how to modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code below produces an error message #3464,"Data Type Mismatch
in
Criteria Expression" and I would appreciate knowing how to correct
the
criteria expression. The code stops running on the next to last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Guest]

Thank you.
When I try thls the line returns a zero. There are no nulls in the table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

If DLookup doesn't find a matching entry, it will return Null. I suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Please bear with me, but now I get an error message "Invalid use of null'
There are no nulls in MyDomain
Thank you for your patience

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
It is text and I would appeciate it if you would tell me how to modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code below produces an error message #3464,"Data Type Mismatch
in
Criteria Expression" and I would appreciate knowing how to correct
the
criteria expression. The code stops running on the next to last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Douglas J. Steele]

Check to make sure that the value you're expecting is in MyCriteria.

You can either write them to the Immediate Window (which you get to using
Ctrl-G) using Debug.Print:

Debug.Print "MyCriteria = " & MyCriteria

or you can use a Message Box:

MsgBox "MyCriteria = " & MyCriteria

You might also double check what's in MyDayExp, MyWeekExp and MyDomain.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you.
When I try thls the line returns a zero. There are no nulls in the table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

If DLookup doesn't find a matching entry, it will return Null. I suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz
function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Please bear with me, but now I get an error message "Invalid use of
null'
There are no nulls in MyDomain
Thank you for your patience

:

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
It is text and I would appeciate it if you would tell me how to
modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code below produces an error message #3464,"Data Type
Mismatch
in
Criteria Expression" and I would appreciate knowing how to
correct
the
criteria expression. The code stops running on the next to last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Guest]

Thanks Doug,
But I'm stil scratching my head. The message box produces:
MyCriteria = CabinNum = '213'
When I pass the pointer over the variable while code is stopped,I get:
MyCriteria = "CabinNum = '213' "
MyDomain = "tblMemberRates"
MyDayExp = "SumrDay"
But the lookup still equals "0"

Check to make sure that the value you're expecting is in MyCriteria.

You can either write them to the Immediate Window (which you get to using
Ctrl-G) using Debug.Print:

Debug.Print "MyCriteria = " & MyCriteria

or you can use a Message Box:

MsgBox "MyCriteria = " & MyCriteria

You might also double check what's in MyDayExp, MyWeekExp and MyDomain.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you.
When I try thls the line returns a zero. There are no nulls in the table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

If DLookup doesn't find a matching entry, it will return Null. I suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz
function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Please bear with me, but now I get an error message "Invalid use of
null'
There are no nulls in MyDomain
Thank you for your patience

:

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
It is text and I would appeciate it if you would tell me how to
modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code below produces an error message #3464,"Data Type
Mismatch
in
Criteria Expression" and I would appreciate knowing how to
correct
the
criteria expression. The code stops running on the next to last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Guest]

Sorry, I forgot to mention that those last two results match my field name
and table name.

Check to make sure that the value you're expecting is in MyCriteria.

You can either write them to the Immediate Window (which you get to using
Ctrl-G) using Debug.Print:

Debug.Print "MyCriteria = " & MyCriteria

or you can use a Message Box:

MsgBox "MyCriteria = " & MyCriteria

You might also double check what's in MyDayExp, MyWeekExp and MyDomain.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you.
When I try thls the line returns a zero. There are no nulls in the table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

If DLookup doesn't find a matching entry, it will return Null. I suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz
function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Please bear with me, but now I get an error message "Invalid use of
null'
There are no nulls in MyDomain
Thank you for your patience

:

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
It is text and I would appeciate it if you would tell me how to
modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code below produces an error message #3464,"Data Type
Mismatch
in
Criteria Expression" and I would appreciate knowing how to
correct
the
criteria expression. The code stops running on the next to last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Tom Lake]

Thanks Doug,
But I'm stil scratching my head. The message box produces:
MyCriteria = CabinNum = '213'
When I pass the pointer over the variable while code is stopped,I get:
MyCriteria = "CabinNum = '213' "

If CabinNum is a numeric field, it's getting the wrong data. '213' is a
string.

Tom Lake

 

[Guest]

Thanks Tom,
But CabinNum is a text field.

If CabinNum is a numeric field, it's getting the wrong data. '213' is a
string.

Tom Lake

 

[Guest]

Thank you John,
But that correction was already made based on an earlier comment from Doug

The code below produces an error message #3464,"Data Type Mismatch in
Criteria Expression" and I would appreciate knowing how to correct the
criteria expression. The code stops running on the next to last line, and the
Criteria shown when the pointer is passed over MyCriteria is: "[CabinNum] =
208", for examlple.

I'm guessing that the CabinNum field is of Text datatype rather than
Number (I'd certainly recommend that choice anyway); if so you need
quotemarks to delimit it:

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = '" & MyCabNum & "'"


John W. Vinson[MVP]

 

[Douglas J. Steele]

I can't see anything obviously wrong.

What happens if you do to the Immediate Window (Ctrl-G), type

?DLookup("SumrDay", "tblMemberRates", "CabinNum = '213'")

and hit Enter?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
But I'm stil scratching my head. The message box produces:
MyCriteria = CabinNum = '213'
When I pass the pointer over the variable while code is stopped,I get:
MyCriteria = "CabinNum = '213' "
MyDomain = "tblMemberRates"
MyDayExp = "SumrDay"
But the lookup still equals "0"

Check to make sure that the value you're expecting is in MyCriteria.

You can either write them to the Immediate Window (which you get to using
Ctrl-G) using Debug.Print:

Debug.Print "MyCriteria = " & MyCriteria

or you can use a Message Box:

MsgBox "MyCriteria = " & MyCriteria

You might also double check what's in MyDayExp, MyWeekExp and MyDomain.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you.
When I try thls the line returns a zero. There are no nulls in the
table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

:

If DLookup doesn't find a matching entry, it will return Null. I
suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz
function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Please bear with me, but now I get an error message "Invalid use of
null'
There are no nulls in MyDomain
Thank you for your patience

:

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
It is text and I would appeciate it if you would tell me how to
modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code below produces an error message #3464,"Data Type
Mismatch
in
Criteria Expression" and I would appreciate knowing how to
correct
the
criteria expression. The code stops running on the next to
last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Guest]

Thanks Doug,
The immediate window returns the correct rate.
When I enter exactly the same wording (without the question mark) in the
code, it also returns the correst rate. Then, when I tried substituting the
variable for only the criteria, it returns Null (or 0 if I use Nz).

I can't see anything obviously wrong.

What happens if you do to the Immediate Window (Ctrl-G), type

?DLookup("SumrDay", "tblMemberRates", "CabinNum = '213'")

and hit Enter?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
But I'm stil scratching my head. The message box produces:
MyCriteria = CabinNum = '213'
When I pass the pointer over the variable while code is stopped,I get:
MyCriteria = "CabinNum = '213' "
MyDomain = "tblMemberRates"
MyDayExp = "SumrDay"
But the lookup still equals "0"

Check to make sure that the value you're expecting is in MyCriteria.

You can either write them to the Immediate Window (which you get to using
Ctrl-G) using Debug.Print:

Debug.Print "MyCriteria = " & MyCriteria

or you can use a Message Box:

MsgBox "MyCriteria = " & MyCriteria

You might also double check what's in MyDayExp, MyWeekExp and MyDomain.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you.
When I try thls the line returns a zero. There are no nulls in the
table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

:

If DLookup doesn't find a matching entry, it will return Null. I
suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz
function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Please bear with me, but now I get an error message "Invalid use of
null'
There are no nulls in MyDomain
Thank you for your patience

:

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
It is text and I would appeciate it if you would tell me how to
modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The code below produces an error message #3464,"Data Type
Mismatch
in
Criteria Expression" and I would appreciate knowing how to
correct
the
criteria expression. The code stops running on the next to
last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Douglas J. Steele]

Sorry, I'm out of ideas. I just double-checked that DLookup does work with
all variables, and it does.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
The immediate window returns the correct rate.
When I enter exactly the same wording (without the question mark) in the
code, it also returns the correst rate. Then, when I tried substituting
the
variable for only the criteria, it returns Null (or 0 if I use Nz).

I can't see anything obviously wrong.

What happens if you do to the Immediate Window (Ctrl-G), type

?DLookup("SumrDay", "tblMemberRates", "CabinNum = '213'")

and hit Enter?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
But I'm stil scratching my head. The message box produces:
MyCriteria = CabinNum = '213'
When I pass the pointer over the variable while code is stopped,I get:
MyCriteria = "CabinNum = '213' "
MyDomain = "tblMemberRates"
MyDayExp = "SumrDay"
But the lookup still equals "0"

:

Check to make sure that the value you're expecting is in MyCriteria.

You can either write them to the Immediate Window (which you get to
using
Ctrl-G) using Debug.Print:

Debug.Print "MyCriteria = " & MyCriteria

or you can use a Message Box:

MsgBox "MyCriteria = " & MyCriteria

You might also double check what's in MyDayExp, MyWeekExp and
MyDomain.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you.
When I try thls the line returns a zero. There are no nulls in the
table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

:

If DLookup doesn't find a matching entry, it will return Null. I
suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz
function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Please bear with me, but now I get an error message "Invalid use
of
null'
There are no nulls in MyDomain
Thank you for your patience

:

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
It is text and I would appeciate it if you would tell me how
to
modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
The code below produces an error message #3464,"Data Type
Mismatch
in
Criteria Expression" and I would appreciate knowing how to
correct
the
criteria expression. The code stops running on the next to
last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria
is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Guest]

I have redone and renamed all the tables and fields to eliminate possible
shaky references and now can get the desired output intermittantly. The code
works OK on the last three records in the lookup tables but still produces a
zero on the first twelve. The lookup tables have the cabin numbers in
sequence from lowest to highest but there is a gap in the sequence. The cabin
numbers are 102 - 108 then 206 - 213. Could my problem be due to the code
runing too fast?

Sorry, I'm out of ideas. I just double-checked that DLookup does work with
all variables, and it does.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
The immediate window returns the correct rate.
When I enter exactly the same wording (without the question mark) in the
code, it also returns the correst rate. Then, when I tried substituting
the
variable for only the criteria, it returns Null (or 0 if I use Nz).

I can't see anything obviously wrong.

What happens if you do to the Immediate Window (Ctrl-G), type

?DLookup("SumrDay", "tblMemberRates", "CabinNum = '213'")

and hit Enter?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
But I'm stil scratching my head. The message box produces:
MyCriteria = CabinNum = '213'
When I pass the pointer over the variable while code is stopped,I get:
MyCriteria = "CabinNum = '213' "
MyDomain = "tblMemberRates"
MyDayExp = "SumrDay"
But the lookup still equals "0"

:

Check to make sure that the value you're expecting is in MyCriteria.

You can either write them to the Immediate Window (which you get to
using
Ctrl-G) using Debug.Print:

Debug.Print "MyCriteria = " & MyCriteria

or you can use a Message Box:

MsgBox "MyCriteria = " & MyCriteria

You might also double check what's in MyDayExp, MyWeekExp and
MyDomain.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you.
When I try thls the line returns a zero. There are no nulls in the
table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

:

If DLookup doesn't find a matching entry, it will return Null. I
suspect
that's the problem.

Decide on what default value you want in that case, and use the Nz
function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Please bear with me, but now I get an error message "Invalid use
of
null'
There are no nulls in MyDomain
Thank you for your patience

:

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
It is text and I would appeciate it if you would tell me how
to
modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
The code below produces an error message #3464,"Data Type
Mismatch
in
Criteria Expression" and I would appreciate knowing how to
correct
the
criteria expression. The code stops running on the next to
last
line,
and
the
Criteria shown when the pointer is passed over MyCriteria
is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.

 

[Douglas J. Steele]

Never heard of problems with timing in situations like this.

You're sure there's no extraneous blanks in your criteria: that it's not
looking for '206 ' instead of '206'?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have redone and renamed all the tables and fields to eliminate possible
shaky references and now can get the desired output intermittantly. The
code
works OK on the last three records in the lookup tables but still produces
a
zero on the first twelve. The lookup tables have the cabin numbers in
sequence from lowest to highest but there is a gap in the sequence. The
cabin
numbers are 102 - 108 then 206 - 213. Could my problem be due to the code
runing too fast?

Sorry, I'm out of ideas. I just double-checked that DLookup does work
with
all variables, and it does.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
The immediate window returns the correct rate.
When I enter exactly the same wording (without the question mark) in
the
code, it also returns the correst rate. Then, when I tried substituting
the
variable for only the criteria, it returns Null (or 0 if I use Nz).

:

I can't see anything obviously wrong.

What happens if you do to the Immediate Window (Ctrl-G), type

?DLookup("SumrDay", "tblMemberRates", "CabinNum = '213'")

and hit Enter?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug,
But I'm stil scratching my head. The message box produces:
MyCriteria = CabinNum = '213'
When I pass the pointer over the variable while code is stopped,I
get:
MyCriteria = "CabinNum = '213' "
MyDomain = "tblMemberRates"
MyDayExp = "SumrDay"
But the lookup still equals "0"

:

Check to make sure that the value you're expecting is in
MyCriteria.

You can either write them to the Immediate Window (which you get to
using
Ctrl-G) using Debug.Print:

Debug.Print "MyCriteria = " & MyCriteria

or you can use a Message Box:

MsgBox "MyCriteria = " & MyCriteria

You might also double check what's in MyDayExp, MyWeekExp and
MyDomain.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you.
When I try thls the line returns a zero. There are no nulls in
the
table;
here it is:
CabinNum SumrDay Etc.
102 $85.00
103 $85.00
104 $85.00
105 $85.00
106 $85.00
206 $95.00
207 $95.00
Etc.
CabinNum is text and SumrDay is currency

:

If DLookup doesn't find a matching entry, it will return Null. I
suspect
that's the problem.

Decide on what default value you want in that case, and use the
Nz
function
to substitute the default in that case:

MyDayRate = Nz(DLookup(MyDayExp, MyDomain, MyCriteria), 1)
MyWeekRate = Nz(DLookup(MyWeekExp, MyDomain, MyCriteria), 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Please bear with me, but now I get an error message "Invalid
use
of
null'
There are no nulls in MyDomain
Thank you for your patience

:

Sorry: I was going to, but accidentally hit Enter! <g>

MyCriteria = "[CabinNum] = """ & MyCabNum & """"

(that's 3 double quotes in front, 4 double quotes after)

or

MyCriteria = "[CabinNum] = " & Chr$(34) & MyCabNum & Chr$(34)

or

MyCriteria = "[CabinNum] = '" & MyCabNum & "'"

Exagerated for clarity, that last one is


MyCriteria = " [CabinNum] = ' " & MyCabNum & " ' "



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Thanks Doug,
It is text and I would appeciate it if you would tell me
how
to
modify
the
"MyCriteria = " line to put them there.

:

Is CabinNum a numeric field in the table, or is it text?

If it's text, you need quotes around the value:


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
The code below produces an error message #3464,"Data
Type
Mismatch
in
Criteria Expression" and I would appreciate knowing how
to
correct
the
criteria expression. The code stops running on the next
to
last
line,
and
the
Criteria shown when the pointer is passed over
MyCriteria
is:
"[CabinNum]
=
208", for examlple.
Private Sub CalcTotals()
'Lookup the rates
Dim MyDayExp As String
Dim MyWeekExp As String
Dim MyDomain As String
Dim MyCriteria As String
Dim MyCabNum As String
Dim MyDayRate As Currency
Dim MyWeekRate As Currency

MyCabNum = ctlCabinNum
MyCriteria = "[CabinNum] = " & MyCabNum

If Forms!FrmReservations.Member = "Member" Then
MyDomain = "tblMemberRates"
Else
MyDomain = "tblGuestRates"
End If
If Month([ArrDate]) > 4 And Month(ArrDate) < 11 Then
MyDayExp = "SumrDay"
MyWeekExp = "SumrWk"
Else
MyDayExp = "WintDay"
MyWeekExp = "WintWk"
End If
MyDayRate = DLookup(MyDayExp, MyDomain, MyCriteria)
MyWeekRate = DLookup(MyWeekExp, MyDomain, MyCriteria)

End Sub

Thank you for your consideration.