Help with multiple criteria DLookup

[Renee]

Good morning all,

I am having trouble with string vs numeric in a multiple DLookup and
using multiple lines. Here is the code I am trying to finess (I only changed
variable names to music topics, to help me represent my problem). I know the
ampersands are missing if this were written in one line, but am unable to get
this to work. Honestly, I am not even sure if DLookup can use 3 criteria.

Dim stDocName As String
Dim varID As Variant

varID = DLookup("[RecordID]", "tbl_Songs", "[txtArtist]= '" & Me.Artist
& "'" & _
"And [AlbumNum]= " &
Me.AlbumNum & _
" And [txtTrackID] =
'" & Me.TrackID"'")

stDocName = "SongRvw"
If Not IsNull(varID) Then
DoCmd.OpenReport stDocName, acViewPreview, , "[tbl_Songs].[RecordID]
= " & varID
Else
MsgBox "No Record Found.", vbOKOnly
cancel = True
End If

Thank you,
Renee

 

[JohnFol]

I'd personally do an OpenReport with the filter and check the OnNoData
event. . . but notwithstanding that ..

If you do a debug on the DLookup line and type in the following what do you
get?

? "[txtArtist]= '" & Me.Artist & "'" & "And [AlbumNum]= " & Me.AlbumNum &
" And [txtTrackID] = '" & Me.TrackID"'"


Is it a sensible string? What happens if you create a new query with

Select * from tbl_songs where

and then paste in the debug result, ie

Select * from tbl_songs where txtArtist='pop Singer' and AlbumNum=4 and
txtTrackID = '1'

PS it looks like txtTrackID should be text going on teh prefix, so do you
need quotes??

 

[Ken Snell [MVP]]

DLookup can take multiple criteria.

You are missing a space in front of the And before the AlbumNum part.

varID = DLookup("[RecordID]", "tbl_Songs", _
"[txtArtist]= '" & Me.Artist & "'" & _
" And [AlbumNum]= " & Me.AlbumNum & _
" And [txtTrackID] = '" & Me.TrackID"'")

 

[Renee]

Thank you! Here is the final version, and it is working wonderfully, thank
you again:
varID = DLookup("[RecordID]", "tbl_Songs", _
"[txtArtist]= '" & Me.Artist & "'" & _
" And [AlbumNum]= " & Me.AlbumNum & _
" And [txtTrackID] = '" & Me.TrackID & "'")

DLookup can take multiple criteria.

You are missing a space in front of the And before the AlbumNum part.

varID = DLookup("[RecordID]", "tbl_Songs", _
"[txtArtist]= '" & Me.Artist & "'" & _
" And [AlbumNum]= " & Me.AlbumNum & _
" And [txtTrackID] = '" & Me.TrackID"'")

--

Ken Snell
<MS ACCESS MVP>

Good morning all,

I am having trouble with string vs numeric in a multiple DLookup and
using multiple lines. Here is the code I am trying to finess (I only
changed
variable names to music topics, to help me represent my problem). I know
the
ampersands are missing if this were written in one line, but am unable to
get
this to work. Honestly, I am not even sure if DLookup can use 3 criteria.

Dim stDocName As String
Dim varID As Variant

varID = DLookup("[RecordID]", "tbl_Songs", "[txtArtist]= '" & Me.Artist
& "'" & _
"And [AlbumNum]= " &
Me.AlbumNum & _
" And [txtTrackID] =
'" & Me.TrackID"'")

stDocName = "SongRvw"
If Not IsNull(varID) Then
DoCmd.OpenReport stDocName, acViewPreview, ,
"[tbl_Songs].[RecordID]
= " & varID
Else
MsgBox "No Record Found.", vbOKOnly
cancel = True
End If

Thank you,
Renee

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Thank you! Here is the final version, and it is working wonderfully, thank
you again:
varID = DLookup("[RecordID]", "tbl_Songs", _
"[txtArtist]= '" & Me.Artist & "'" & _
" And [AlbumNum]= " & Me.AlbumNum & _
" And [txtTrackID] = '" & Me.TrackID & "'")

DLookup can take multiple criteria.

You are missing a space in front of the And before the AlbumNum part.

varID = DLookup("[RecordID]", "tbl_Songs", _
"[txtArtist]= '" & Me.Artist & "'" & _
" And [AlbumNum]= " & Me.AlbumNum & _
" And [txtTrackID] = '" & Me.TrackID"'")

--

Ken Snell
<MS ACCESS MVP>

Good morning all,

I am having trouble with string vs numeric in a multiple DLookup and
using multiple lines. Here is the code I am trying to finess (I only
changed
variable names to music topics, to help me represent my problem). I
know
the
ampersands are missing if this were written in one line, but am unable
to
get
this to work. Honestly, I am not even sure if DLookup can use 3
criteria.

Dim stDocName As String
Dim varID As Variant

varID = DLookup("[RecordID]", "tbl_Songs", "[txtArtist]= '" &
Me.Artist
& "'" & _
"And [AlbumNum]=
" &
Me.AlbumNum & _
" And
[txtTrackID] =
'" & Me.TrackID"'")

stDocName = "SongRvw"
If Not IsNull(varID) Then
DoCmd.OpenReport stDocName, acViewPreview, ,
"[tbl_Songs].[RecordID]
= " & varID
Else
MsgBox "No Record Found.", vbOKOnly
cancel = True
End If

Thank you,
Renee