Hi James
In the case of getting complex SQL queries written,
if you have a warchest of pre-written string formatting
code you generate them with better quicker results.
It is a case of having the solution at hand rather than having
to respond to the vagaries of the moment.
At first sight the following probably looks like more work,
but if the sub components are the result of years of reuse
the cut and paste aspect rather than being a case of errors
as in most cases; becomes instead a source of well known tried
tested and bug free assistance.
When you come to creating search forms where there is an
arbitrary number of fields to search with different criteria
including "=" and "LIKE" for text fields it will save your bacon.
Regards - John
Private Sub Load_tblPropellantForm()
Dim strWhereClause As String
Dim strPerCentClause As String
Dim HiPCT As Double
Dim LoPCT As Double
HiPCT = Nz(LowPCT, 0)
LoPCT = Nz(LowPCT, 0)
If HiPCT = LoPCT Then
strPerCentClause = " = " & LoPCT " & vbNewLine
Else
strPerCentClause = "BETWEEN " & LoPCT & " AND " & HoPCT & vbNewLine
End If
strWhereClause = ""
strWhereClause = AppendClause(strWhereClause, "[OX1NAM] = '" & Me!Oxidizer &
"', "AND") ' comment allowed
strWhereClause = AppendClause(strWhereClause, "[OX1PCT] = '" &
strPerCentClause, "AND")
strWhereClause = AppendClause(strWhereClause, "[OX2NAM] = '" & Me!Oxidizer &
"', "AND")
strWhereClause = AppendClause(strWhereClause, "[OX2PCT] = '" &
strPerCentClause, "AND")
strWhereClause = AppendClause(strWhereClause, "[OX3NAM] = '" & Me!Oxidizer &
"', "AND")
strWhereClause = AppendClause(strWhereClause, "[OX3PCT] = '" &
strPerCentClause, "AND")
strWhereClause = AppendClause(strWhereClause, "[OX4NAM] = '" & Me!Oxidizer &
"', "AND")
strWhereClause = AppendClause(strWhereClause, "[OX4PCT] = '" &
strPerCentClause, "AND")
strWhereClause = AppendClause(strWhereClause, "[OX5NAM] = '" & Me!Oxidizer &
"', "AND")
strWhereClause = AppendClause(strWhereClause, "[OX5PCT] = '" &
strPerCentClause, "AND")
'Debug.Print strWhereClause ' Un comment to trouble shoot
DoCmd.OpenForm "tblPropellant Query", , ,strWhereClause
End Sub
' Single demo function
Function AppendClause(Original As String, ExtraClause As String, Separator
As String) As String
If Original & "" = "" Then
AppendClause = ExtraClause
Exit Function
End If
If ExtraClause & "" = "" Then
AppendClause = Original
Exit Function
End If
AppendClause = Original & " " Separator & " " & ExtraClause & " " &
vbNewLine
End Function
James said:
Chaim,
I found it, I had left one of the ' in the code before the or statement.
Thank You Very Much!!
James
Chaim said:
James,
There are 3 or 4 errors here.
1. Where you get the 'Expected end of statement'- remove one of the quote
characters.
2. Wherever you have " And " & OX?PCT between- remove the " & characters.
There are 3 of these situations.
I think that catches all of the syntax issues. I wouldn't want to guarantee
that though.
Good Luck!
--
Chaim
:
DoCmd.OpenForm "tblPropellant Query", , ,"[OX1NAM]= '" & Me!Oxidizer & "' And
" & " " OX1PCT between " & Nz(LowPCT.Value, 0) _
^ I get an expected end of statement here.
& " and " & Nz(HighPCT.Value, 0) & " OR [OX2NAM]= '" & Me!Oxidizer &
"' And " & OX2PCT between " & Nz(LowPCT.Value, 0) _
& " and " & Nz(HighPCT.Value, 0) & " OR [OX3NAM]= '" & Me!Oxidizer &
"' And " & OX3PCT between " & Nz(LowPCT.Value,0) _
& " and " & Nz(HighPCT.Value, 0) & "' OR [OX4NAM]= '" & Me!Oxidizer
& "' And " & OX4PCT between " & Nz(LowPCT.Value, 0) _
& " and " & Nz(HighPCT.Value, 0)
I get an end of statement message at the point indicated.
You are right though, there should be a more efficient way of sending code
accross a program like this. It is very difficult to see what is happening.
Thanks for your help,
James
:
DoCmd.OpenForm "tblPropellant Query", , ,
"[OX1NAM]= '" & Me!Oxidizer & "' And " & "OX1PCT between " _
^ Add a
quote here
& Nz(LowPCT.Value, 0) & " and " & Nz(HighPCT.Value, 0)
& "' OR [OX2NAM]= '" & Me!Oxidizer _
^ why is this here? Should not be.
& "' And " & OX2PCT between " & Nz(LowPCT.Value, 0) & " and " &
Nz(HighPCT.Value, 0) & "' OR [OX3NAM]= '" _
^ why is this here? Should not be.
& Me!Oxidizer & "' And " & OX3PCT between " & Nz(LowPCT.Value,
0) & " and " & Nz(HighPCT.Value, 0) _
& "' OR [OX4NAM]= '" & Me!Oxidizer & "' And " & OX4PCT between "
& Nz(LowPCT.Value, 0) & " and " & Nz(HighPCT.Value, 0)
I probably didn't get all of them. This is REAL HARD to read, certainly in
the News reader I'm using. In general, you seem to have extraneous
apostrophes/single quotes (') surrounding all of your OR clauses.
Good Luck! I hope this doesn't look like this for you. No wonder you're
having problems sorting this out.
--
Chaim
:
JackP,
ok I think I removed the quotes you siad to, but now I get a complie error:
Syntax Error. My code looks like this now:
DoCmd.OpenForm "tblPropellant Query", , , "[OX1NAM]= '" &
Me!Oxidizer & "' And " & OX1PCT between " _
& Nz(LowPCT.Value, 0) & " and " & Nz(HighPCT.Value, 0) & "' OR
[OX2NAM]= '" & Me!Oxidizer _
& "' And " & OX2PCT between " & Nz(LowPCT.Value, 0) & " and " &
Nz(HighPCT.Value, 0) & "' OR [OX3NAM]= '" _
& Me!Oxidizer & "' And " & OX3PCT between " & Nz(LowPCT.Value,
0) & " and " & Nz(HighPCT.Value, 0) _
& "' OR [OX4NAM]= '" & Me!Oxidizer & "' And " & OX4PCT between "
& Nz(LowPCT.Value, 0) & " and " & Nz(HighPCT.Value, 0)
As for it being a NHS abrieviation... I don't know what NHS means but it is
the actual name of the field, and it is an abrieviation for Oxidizer 1
Percent ect.
-James
:
actually, it looks like you've repeated this every time you've got a line I
mentioned (with OX1PCT, OX2PCT, OX3PCT etc).
Incidentally, is that some sort of NHS abbreviation?
