Several query results in 1 query? (or another method?)

[chris-uk-lad]

Im a little troubled by something, i need a button on a form that, when clicked, will check my database [Info1] for cell irregularities and post the results in a table. I can manage this easily enough with 1 query but i need several of these to be done via the click of the button but dont wish to use many different querys (only 1 if possible).

So in essence i require a method to allow me to (on click):

Check [Post Code] is populated but [Address 1] is not
Check [Address 2] is populated but [Address 2] is not
Check [Post Code] is populated AND one of either [Address 3] and [Address 4] are not populated.

The query will ammend a form to display these results. I know how to run the query but wish to only use 1 query to run all 3 commands, with the possibility of more commands in future.

Any other questions please ask, i understand its a little vague.

Many thanks

 

[DCrake]

This will require the creation of a Function to derive the answer. You will need to pass all the parameters to the function so they can be evaluated and from that the decision can be made as to the response fed back to the user.

Public Function ValidationTest(Optional Adr1 As String, Optional Adr2 As String, Optional Adr3 As String, Optional Adr4 As String, Optional Postcode As String) As String
'Test one:Check [Post Code] is populated but [Address 1] is not

If Not IsEmpty(Postcode) And IsEmpty(Adr1) Then
   ValidationTest = "A"
   Exit Function
End If

'Test two :Check [Address 2] is populated but [Address 2] is not
'?Typo Same field

If Not IsEmpty(Adr2) And IsEmpty(Adr2) Then
  ValidationTest = "B"
   Exit Function
End If

Test Three:Check [Post Code] is populated AND one of either [Address 3] and [Address 4] are not populated.

If Not IsEmpty(Postcode) Then
   If IsEmpty(Adr3) or IsEmpty(Adr4) Then
       ValidationTest = "C"
       Exit Function
   End If
End If

'If none of the above triggered then fire a exit code
ValidationTest = "Z" 

End Function

Then in your query add a new column

IsValid:ValidationTest([Address 1], [Address 2], [Address 3], [Address 4], [PostCode])

Run the query and examine the results and act accordingly.

You will need to save this function in a module (not the same name as the function).



Aircode Untested


David

 

[Atomic Shrimp]

Could this not just be done with a bunch of and/or criteria in the query?

for example:
SELECT mytable.[Address 1], mytable.[Address 2], mytable.[Address 3], mytable.[Address 4], mytable.[Post Code]
FROM mytable
WHERE (((mytable.[Address 1]) Is Null) AND ((mytable.[Post Code]) Is Not Null))
OR (((mytable.[Address 1]) Is Null) AND ((mytable.[Address 2]) Is Not Null))
OR ((((mytable.[Address 3]) Is Null) OR ((mytable.[Address 4]) Is Null)) AND ((mytable.[Post Code]) Is Not Null));

(line 2 of your criteria didn't make sense, BTW, as Address 2 cannot be both populated and not populated - I assumed you meant Address 1 and Address 2)

 

[Simon_MT]

Here's my approach, if accuracy of input is imperative:

Address1 Mandatory - Table Level Required
Address2 Can be Blank
Town/City Mandatory - Table Level Required
Country Mandatory - Table Level Required
Postcode Mandatory - Table Level Required

The rational is if the entry information is incomplete it might as well not be there.

I have developed this further with Postcodes to even test if they are constructed properly and put input control on the field. I got sick of the CCTV approach and addressed the cause of sloppiness.

Simon