Need help with a dynamic QueryDef

[stinnes]

I'm building a dynamic (QBF) search form for our office library database. I'm no VB wiz, so I extracted a sample script from Microsoft's site which I have modified to fit our DB. It seems to work well for all fields except one, the Authors' LastName and FirstName fields. Here's the offending part of the script:

where = Null
where = where & " AND [TopicID]= " + Me![TopicID] + ""
where = where & " AND [Format]= " + Me![Format] + ""
where = where & " AND [Lastname]= '" + Me![LastName] + "'"
where = where & " AND [FirstName]= '" + Me![FirstName] + "'"
where = where & " AND [Issuer]= " + Me![Issuer] + ""

Searches for Topics, Format and Issuers work perfectly. When I run the script to search for authors, it returns a dialog box asking for the Parameter Value for the FirstName and/or LastName fields. This is not the result I am looking for.

I suspect the problem is caused by the structure of the DB, specifically that the 'Authors' table is linked to the main 'Books' table by an intersection table. As I mentioned above, my knowledge of VB is very limited, so forgive me if this question has an obvious answer. Hopefully I have explained this well enough for you to understand. Any help is greatly appreciated.

 

[stinnes]

In the first paragraph, sentance should read:

It seems to work well for all fields except two...

 

[JerryDennison]

I'm surprised it works at all. Typically you don't concatenate fields using the '+' sign. Use the '&'.

where = Null
where = where & " AND [TopicID]= " & Me![TopicID] & ""
where = where & " AND [Format]= '" & Me![Format] & "'"
where = where & " AND [Lastname]= '" & Me![LastName] & "'"
where = where & " AND [FirstName]= '" & Me![FirstName] & "'"
where = where & " AND [Issuer]= '" & Me![Issuer] & "'"

 

[stinnes]

Thanks for the tip Jerry, however as I said, I just cut-and-pasted the script from the Micorsoft site. The script does work as it is. Except, of course, for the initial problem I described above. Still waiting for some advice on that.