How to make part of a where clause a perameter

[DomFino]

I have vba code in Access 2010 that does bulk emailing based on a query. Currently I have to run this code twice first selecting records between A and L and then a second time to get records between L and Z.

Anyway, I have two identical queries where the between (in red below) is the only difference. I run the code with line one commented out and reverse this for my second run.

Is there a way to make the between criteria a pop up perimeter where I can chose A* and L* or L* and Z*? Seems crazy to have duplicate code to complete this task.


' Set MailList = db.OpenRecordset("qryMasterQuery_WithEmail")
Set MailList = db.OpenRecordset("qryMasterQuery_WithEmail2")


qryMasterQuery_WithEmail

WHERE ((([First] & " " & [Mid] & " " & [Last]) Between "a*" And "l*") AND ((tblMaster.[E-Mail]) Is Not Null) AND ((tblMaster.STATUSTYPEID)=1))
ORDER BY [First] & " " & [Mid] & " " & [Last];

qryMasterQuery_WithEmail2

WHERE ((([First] & " " & [Mid] & " " & [Last]) Between "l*" And "z*") AND ((tblMaster.[E-Mail]) Is Not Null) AND ((tblMaster.STATUSTYPEID)=1))
ORDER BY [First] & " " & [Mid] & " " & [Last];

[Bob Phillips]

Try


' Set MailList = db.OpenRecordset("qryMasterQuery_WithEmail")
Set MailList = Db.OpenRecordset("qryMasterQuery_WithEmail2")

WHERE ((([First] & " " & [Mid] & " " & [Last]) Between param.start.letter & "*" And param.end.letter & "*") AND
((tblMaster.[E-Mail]) Is Not Null) AND
((tblMaster.STATUSTYPEID)=1))
ORDER BY [First] & " " & [Mid] & " " & [Last];

this will prompt you for the start and end letters at runtime.

[DomFino]

Wow! This works perfectly. Thank you so much for your reply.
I spent days researching the web and kept coming up with complex solutions that involved adding a table, creating a pop up form and on and on. Your solution is clean, easy to understand and works like a charm.

Thank you again. I guess it is easy if you know what your are doing:hi: