Access query help

  interzone55 19:33 26 Apr 2005
Locked

I have the following query in an access database, it refers to a form with 4 boxes. the query works if all 4 boxes are full, but if one is empty the query fails, anyone know where I've gone wrong?

WHERE (((Property.AskingPrice) Between [Forms]![Search]![txtMinPrice] And [Forms]![Search]![txtMaxPrice]) AND ((Property.District)=[Forms]![Search]![Area]) AND ((Property.NoBeds)>=[Forms]![Search]![Beds]));

TIA
Alan

  Access Moron 13:41 02 May 2005

you will need to do this on the onclick event of a command button

create the following function

Function BuildWhereString() as string

dim Retrun as string

if trim (" " & txtMinPrice) <> "" then

return = "Property.AskingPrice > " & txtMinPrice

end if

if trim (" " & txtMaxPrice) <> "" then

if trim(" " & Return) <> "" then

Return = Return & " AND "

end if

Return = return & " Property.AskingPrice < " & txtMaxPrice

end if

If trim(" " & Area ) <> "" then

if trim(" " & Return) <> "" then

Return = Return & " AND "

end if

Return = Return & " District = '" & Area & "'"

end if

if trim(" " & Beds) <> "" then

if trim(" " & Return) <> "" then

Return = Return & " AND "

end if

Return = Return & " NoBeds >= " & Beds

end if

If Trim(" " & Return) <> "" then

Return = "WHERE " & Return

end if

BuildWhereString = return

End function

  Access Moron 13:45 02 May 2005

Now
Build a coomand button that opens a form
whith the following code

DoCmd.OpenForm "FormName", acNormal, , BuildWhereString

  interzone55 20:36 03 May 2005

Thanks for the very long answer, I'm slightly puzzled about one thing.

Do I add the code to the "Mouse up" event of a form that includes the txtMinMax etc boxes, or do I have the code elsewhere then put the "doCmd.OPenForm "FormName", acNormal, ,BuildWhereString" on the Mouse Up event?

tia
Alan

  Access Moron 16:27 07 May 2005

onclick event of a command button on the form

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Print designer Kelly Anna on confident mark making & modern femininity

New iMac Pro release date, UK price & specs rumours

Comment créer, utiliser et supprimer son compte Facebook ?