DLookup

  • Thread starter vbnetman via AccessMonster.com
  • Start date
V

vbnetman via AccessMonster.com

I'm running the following code to enable / disable radio buttons, depending
on certain conditions. However, I get a type mismatch on line 5. I've
declared meterfind as an integer. Is DLookup returning an integer? If so, why
the mismatch?

Private Sub Frame38_Click()
Select Case Me.Frame38
Case 1
Dim meterfind As Integer
meterfind = DLookup("[UnitOfMeasure]", "TblRunningMeter", "meterID = 1")
If meterfind = 2 Then
meter2OptionButton.Enabled = True
Else
meter2OptionButton.Enabled = False
End If
End Select
End Sub

Thank you in advance
 
J

Jeff L

Is MeterID defined as an integer in your table? If it is text, then
that would be where the error is coming from because you need single
quotes around a text value.
 
V

vbnetman via AccessMonster.com

Jeff said:
Is MeterID defined as an integer in your table? If it is text, then
that would be where the error is coming from because you need single
quotes around a text value.
I'm running the following code to enable / disable radio buttons, depending
on certain conditions. However, I get a type mismatch on line 5. I've
Click to expand...
[quoted text clipped - 19 lines]
Message posted via AccessMonster.com
Click to expand...
Click to expand...


Jeff,
Data type is number and field size is long integer....change long integer to
just integer?
 
G

Guest

If the field in the table is Long Integer, just leave it that way and change
the Dim in the Sub. Why are you using a Select statement with only one
option. That is not an efficient way to do this, unless this is just a sub
set of your code. Also, use some indenting. It make the code much easier to
read. Although some will disagree, it is better technique to do all your
dimming at the top of the sub. That way, you don't have to look through the
code to figure out what it is. And, qualify your references. You can confuse
Access when you don't.
Also, you DLookup will raise an "Invalid Use of Null" error if you don't
protect against it. Only variant data types can accept a Null. Using the Nz
function with a DLookup is always good.

Private Sub Frame38_Click()
Dim meterfind As Long

Select Case Me.Frame38
Case 1
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter",
"meterID = 1"), 0)
If meterfind = 2 Then
Me.meter2OptionButton.Enabled = True
Else
Me.meter2OptionButton.Enabled = False
End If
'The above If/Then/Else can be done in one line:
Me.meter2OptionButton.Enabled = (meterfind = 2)
End Select
End Sub


vbnetman via AccessMonster.com said:
Jeff said:
Is MeterID defined as an integer in your table? If it is text, then
that would be where the error is coming from because you need single
quotes around a text value.
I'm running the following code to enable / disable radio buttons, depending
on certain conditions. However, I get a type mismatch on line 5. I've
Click to expand...
[quoted text clipped - 19 lines]
Message posted via AccessMonster.com
Click to expand...
Click to expand...


Jeff,
Data type is number and field size is long integer....change long integer to
just integer?
Click to expand...
 
V

vbnetman via AccessMonster.com

Klatuu said:
If the field in the table is Long Integer, just leave it that way and change
the Dim in the Sub. Why are you using a Select statement with only one
option. That is not an efficient way to do this, unless this is just a sub
set of your code. Also, use some indenting. It make the code much easier to
read. Although some will disagree, it is better technique to do all your
dimming at the top of the sub. That way, you don't have to look through the
code to figure out what it is. And, qualify your references. You can confuse
Access when you don't.
Also, you DLookup will raise an "Invalid Use of Null" error if you don't
protect against it. Only variant data types can accept a Null. Using the Nz
function with a DLookup is always good.

Private Sub Frame38_Click()
Dim meterfind As Long
Click to expand...

Klatuu,
Thank you for the response and observations. The example I've shown is part
of a larger bunch of code. I presented it this way for clarity.
Select Case Me.Frame38
Case 1
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter",
"meterID = 1"), 0)
If meterfind = 2 Then
Me.meter2OptionButton.Enabled = True
Else
Me.meter2OptionButton.Enabled = False
End If
'The above If/Then/Else can be done in one line:
Me.meter2OptionButton.Enabled = (meterfind = 2)
End Select
End Sub
[quoted text clipped - 9 lines]
Data type is number and field size is long integer....change long integer to
just integer?
Click to expand...
Click to expand...
 
V

vbnetman via AccessMonster.com

Klatuu said:
If the field in the table is Long Integer, just leave it that way and change
the Dim in the Sub. Why are you using a Select statement with only one
option. That is not an efficient way to do this, unless this is just a sub
set of your code. Also, use some indenting. It make the code much easier to
read. Although some will disagree, it is better technique to do all your
dimming at the top of the sub. That way, you don't have to look through the
code to figure out what it is. And, qualify your references. You can confuse
Access when you don't.
Also, you DLookup will raise an "Invalid Use of Null" error if you don't
protect against it. Only variant data types can accept a Null. Using the Nz
function with a DLookup is always good.

Private Sub Frame38_Click()
Dim meterfind As Long

Select Case Me.Frame38
Case 1
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter",
"meterID = 1"), 0)
If meterfind = 2 Then
Me.meter2OptionButton.Enabled = True
Else
Me.meter2OptionButton.Enabled = False
End If
'The above If/Then/Else can be done in one line:
Me.meter2OptionButton.Enabled = (meterfind = 2)
End Select
End Sub
[quoted text clipped - 9 lines]
Data type is number and field size is long integer....change long integer to
just integer?
Click to expand...
Click to expand...





Klatuu,

Here's what I've got which produces thesame mismatch error. Any thoughts?

Private Sub Frame38_Click()
Dim meterfind As Long

Select Case Me.Frame38
Case 1
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter", "meterID
= 1"), 0)
If meterfind = 2 Then
Me.meter2OptionButton.Enabled = True
Else
Me.meter2OptionButton.Enabled = False
End If
End Select
End Sub

Thank you
 
G

Guest

That's what I thought. I really don't mean to be overly critical, I am
really trying to help. I have been coding in multiple languages for almost
30 years and my observations are based on training and experience.

I try to keep my code as clean and readable as possible. Other people may
have to read and understand my code. Six months from now, I may have figure
out what I did then :)

vbnetman via AccessMonster.com said:
Klatuu said:
If the field in the table is Long Integer, just leave it that way and change
the Dim in the Sub. Why are you using a Select statement with only one
option. That is not an efficient way to do this, unless this is just a sub
set of your code. Also, use some indenting. It make the code much easier to
read. Although some will disagree, it is better technique to do all your
dimming at the top of the sub. That way, you don't have to look through the
code to figure out what it is. And, qualify your references. You can confuse
Access when you don't.
Also, you DLookup will raise an "Invalid Use of Null" error if you don't
protect against it. Only variant data types can accept a Null. Using the Nz
function with a DLookup is always good.

Private Sub Frame38_Click()
Dim meterfind As Long
Click to expand...

Klatuu,
Thank you for the response and observations. The example I've shown is part
of a larger bunch of code. I presented it this way for clarity.
Select Case Me.Frame38
Case 1
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter",
"meterID = 1"), 0)
If meterfind = 2 Then
Me.meter2OptionButton.Enabled = True
Else
Me.meter2OptionButton.Enabled = False
End If
'The above If/Then/Else can be done in one line:
Me.meter2OptionButton.Enabled = (meterfind = 2)
End Select
End Sub
Is MeterID defined as an integer in your table? If it is text, then
that would be where the error is coming from because you need single
Click to expand...
[quoted text clipped - 9 lines]
Data type is number and field size is long integer....change long integer to
just integer?
Click to expand...
Click to expand...
Click to expand...
 
V

vbnetman via AccessMonster.com

Point well taken...
I'm not sure if I posted a response in the right area...did you see it?
That's what I thought. I really don't mean to be overly critical, I am
really trying to help. I have been coding in multiple languages for almost
30 years and my observations are based on training and experience.

I try to keep my code as clean and readable as possible. Other people may
have to read and understand my code. Six months from now, I may have figure
out what I did then :)
If the field in the table is Long Integer, just leave it that way and change
the Dim in the Sub. Why are you using a Select statement with only one [quoted text clipped - 34 lines]
Data type is number and field size is long integer....change long integer to
just integer?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

the data type for [UnitOfMeasure] and meterfind need to be the same.
your code expects meterid (which should be [meterid] in your code) should be
a numeric field in the table.
If [meterid] is numeric:
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter", "[meterID] =
1"), 0)
If [meterid] is text
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter", "[meterID] =
'1'"), 0)
If UnitOfMeasure is text, then in addition to chaning meterfind to string,
you need to change the Nz part of the code
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter", "[meterID] =
1"), "")

vbnetman via AccessMonster.com said:
Klatuu said:
If the field in the table is Long Integer, just leave it that way and change
the Dim in the Sub. Why are you using a Select statement with only one
option. That is not an efficient way to do this, unless this is just a sub
set of your code. Also, use some indenting. It make the code much easier to
read. Although some will disagree, it is better technique to do all your
dimming at the top of the sub. That way, you don't have to look through the
code to figure out what it is. And, qualify your references. You can confuse
Access when you don't.
Also, you DLookup will raise an "Invalid Use of Null" error if you don't
protect against it. Only variant data types can accept a Null. Using the Nz
function with a DLookup is always good.

Private Sub Frame38_Click()
Dim meterfind As Long

Select Case Me.Frame38
Case 1
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter",
"meterID = 1"), 0)
If meterfind = 2 Then
Me.meter2OptionButton.Enabled = True
Else
Me.meter2OptionButton.Enabled = False
End If
'The above If/Then/Else can be done in one line:
Me.meter2OptionButton.Enabled = (meterfind = 2)
End Select
End Sub
Is MeterID defined as an integer in your table? If it is text, then
that would be where the error is coming from because you need single
Click to expand...
[quoted text clipped - 9 lines]
Data type is number and field size is long integer....change long integer to
just integer?
Click to expand...
Click to expand...





Klatuu,

Here's what I've got which produces thesame mismatch error. Any thoughts?

Private Sub Frame38_Click()
Dim meterfind As Long

Select Case Me.Frame38
Case 1
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter", "meterID
= 1"), 0)
If meterfind = 2 Then
Me.meter2OptionButton.Enabled = True
Else
Me.meter2OptionButton.Enabled = False
End If
End Select
End Sub

Thank you
Click to expand...
 
V

vbnetman via AccessMonster.com

Ah -Haa....unit of measure IS text, the other are not. I will give it a shot.
the data type for [UnitOfMeasure] and meterfind need to be the same.
your code expects meterid (which should be [meterid] in your code) should be
a numeric field in the table.
If [meterid] is numeric:
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter", "[meterID] =
1"), 0)
If [meterid] is text
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter", "[meterID] =
'1'"), 0)
If UnitOfMeasure is text, then in addition to chaning meterfind to string,
you need to change the Nz part of the code
meterfind = Nz(DLookup("[UnitOfMeasure]", "TblRunningMeter", "[meterID] =
1"), "")
[quoted text clipped - 51 lines]
Thank you
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dlookup 4
PowerPoint Shuffle (swap) objects on PPT slides with the same name only 0
Access Can't Get Dlookup To Work 1
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Using A Dlookup Function On A Form When A Combo Box is Updated 2
Preventing Null Value based dependent on data in other field 1
Error: Compile Error,ByRef argument type mismatch 0
DLookup with Multiple key field criteria in same file 5

Top