Dlookup

[vbnetman via AccessMonster.com]

In the following line of code, is there a way to search the meterID field for
a value >= to 1 rather than a specific value.


meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID=1"), 0)


to return something like:

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID=1"), 0)
if meterFind = 1 then do something
if meterFind = 2 then do something else
end if
end if

 

[Klatuu]

Sure, just use what you posted. I'm suprised you didn't try it already

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID>=1"), 0)

The only problem is, it will alway return the first meterID it finds. If,
for example, your recordset is ordered by meterID, it will always return 1.

If you are trying to find a specific meter, then you need a way to tell the
code which meter to look for. This would typically be done with either a
text box or a combo box. For example purposes, lets use a text box, Then,
in the After Update of the text box or in the Click event of a command button:

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID>=" &
Me.txtMeterID), 0)

As to the second part, I would use a Select Case statment rather than the
Ifs. The issue here is, how many meters do you have? Will any be added in
the future? That would make a difference in how I designed the code. What
would you do different for different meters?

Select Case meterfind
Case Is = 1
do something
Case Is =2
do something else
Case Esle
Cant find the meter
End Select

 

[vbnetman via AccessMonster.com]

Here’s a visual of sorts.
A maximum of 4 meters per unit will be permitted with my design. The
arrangement (simplified) is:

tblMeters
unitID – PK
meterID – PK
description (typically hour or odometer)

The form I’m using consists of a meter group box containing 4 radio buttons.
If a unit has only 2 meters, 2 buttons are enabled and 2 are disabled. To the
right of that are 3 text boxes; UnitID, MeterID and description where a user
can view the type of each meter for a given unit. Obviously there are other
intricacies like adding a new meter etc., but this part of the project is
what I’m focusing on

Sure, just use what you posted. I'm suprised you didn't try it already

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID>=1"), 0)

The only problem is, it will alway return the first meterID it finds. If,
for example, your recordset is ordered by meterID, it will always return 1.

If you are trying to find a specific meter, then you need a way to tell the
code which meter to look for. This would typically be done with either a
text box or a combo box. For example purposes, lets use a text box, Then,
in the After Update of the text box or in the Click event of a command button:

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID>=" &
Me.txtMeterID), 0)

As to the second part, I would use a Select Case statment rather than the
Ifs. The issue here is, how many meters do you have? Will any be added in
the future? That would make a difference in how I designed the code. What
would you do different for different meters?

Select Case meterfind
Case Is = 1
do something
Case Is =2
do something else
Case Esle
Cant find the meter
End Select

In the following line of code, is there a way to search the meterID field for
a value >= to 1 rather than a specific value.

[quoted text clipped - 8 lines]

end if
end if

 

[vbnetman via AccessMonster.com]

Here's part of the code:

''Search the records to see if there are any meters. This program will allow
''up to 4 meters so the search will look for 1,2,3 and 4. If not found then
''that corrsponding radio button is disabled.
'
meterfind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID=1"), 0)
If meterfind = 1 Then
Me.meter1OptionButton.Enabled = True
'Find out the description of meter 1. DLookup returns a string like
'hours, miles, odometer etc. and meterfind stores it.
meter1Description = DLookup("[UnitOfMeasure]", "TblRunningMeter",
"meterID = 1")
meterType = meter1Description

Sure, just use what you posted. I'm suprised you didn't try it already

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID>=1"), 0)

The only problem is, it will alway return the first meterID it finds. If,
for example, your recordset is ordered by meterID, it will always return 1.

If you are trying to find a specific meter, then you need a way to tell the
code which meter to look for. This would typically be done with either a
text box or a combo box. For example purposes, lets use a text box, Then,
in the After Update of the text box or in the Click event of a command button:

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID>=" &
Me.txtMeterID), 0)

As to the second part, I would use a Select Case statment rather than the
Ifs. The issue here is, how many meters do you have? Will any be added in
the future? That would make a difference in how I designed the code. What
would you do different for different meters?

Select Case meterfind
Case Is = 1
do something
Case Is =2
do something else
Case Esle
Cant find the meter
End Select

In the following line of code, is there a way to search the meterID field for
a value >= to 1 rather than a specific value.

[quoted text clipped - 8 lines]

end if
end if

 

[vbnetman via AccessMonster.com]

Klatuu,
Part of what I'm trying to show is how many meters are assigned to a unit
via the enabling / disabling of radio buttons. Let's say a unit has 2 meters
assigned. 2 radio buttons are enabled and 2 are not. Clicking on an enabled
radio button displays the unit ID, meterID and description. That's a synopsis

Sure, just use what you posted. I'm suprised you didn't try it already

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID>=1"), 0)

The only problem is, it will alway return the first meterID it finds. If,
for example, your recordset is ordered by meterID, it will always return 1.

If you are trying to find a specific meter, then you need a way to tell the
code which meter to look for. This would typically be done with either a
text box or a combo box. For example purposes, lets use a text box, Then,
in the After Update of the text box or in the Click event of a command button:

meterFind = Nz(DLookup("[meterID]", "TblRunningMeter", "meterID>=" &
Me.txtMeterID), 0)

As to the second part, I would use a Select Case statment rather than the
Ifs. The issue here is, how many meters do you have? Will any be added in
the future? That would make a difference in how I designed the code. What
would you do different for different meters?

Select Case meterfind
Case Is = 1
do something
Case Is =2
do something else
Case Esle
Cant find the meter
End Select

In the following line of code, is there a way to search the meterID field for
a value >= to 1 rather than a specific value.

[quoted text clipped - 8 lines]

end if
end if