Query format

[Dan]

I have an access database that links in some SQL tables. There is a form
where end users select options for what they want to display. Once an option
button is selected the text box associated with it is enabled and they can
enter in thier criteria. I have this working for two but can not get the
third. I am sure it has to do with the format the code I have is as follows:

Private Sub Command5_Click()
Dim a As String
Dim CritS As String
Dim CritW As String
Dim CritO As String

CritS = "SELECT PostJobs.COMPL_DTE_OJB AS [Comp], PostJobs.ORDER_NO_OJB AS
[Order], PostJobs.JOB_SEQ_NO_OJB, PostJobs.JOB_NO_OJB AS Job,
PostJobs.JOB_TYP_OJB AS Type, PostJobs.IR_TECH_OJB AS Tech,
PostJobs.QC_STATUS, PostJobs.QC_REP, PostJobs.QC_DATE FROM PostJobs "

CritO = "ORDER BY PostJobs.IR_TECH_OJB, PostJobs.COMPL_DTE_OJB; "

If Me.Option6 Or Me.Option9 Then
' CritW = "WHERE (((PostJobs.IR_TECH_OJB) = '" & Me.Text3 & "') And
((PostJobs.COMPL_DTE_OJB) = #" & Me.Text8 & "#))"

' If Me.Option9 = False Then ' TEXT FIELD
' CritW = "WHERE (((PostJobs.IR_TECH_OJB)='" & Me.Text3 & "')) "
' End If

' If Me.Option6 = False Then 'DATE FIELD
' CritW = "WHERE (((PostJobs.COMPL_DTE_OJB) = #" & Me.Text8 & "#)) "
' End If

If Me.Option13 = True Then 'Number Field
CritW = "WHERE (((PostJobs.JOB_NO_OJB)='" & Me.Text12 & "')) "
End If

'End If
Me.List0.RowSource = CritS & CritW & CritO
End Sub

If I use the CritW options for the other 2 and still use Text12 it works, I
know, or strongly feel, its the format for the number field, but I'll be
buggered if I can find it. It's probably simply and I am over thinking it.

Thanks for the help in advance

 

[Sylvain Lafontaine]

First, this newsgroup is about ADP and SQL-Server and has nothing to do with
Access' JET, MDB & ACCDB database files or ODBC linked tables; so you would
increase your chances of getting an adequate response in a timely manner by
posting to a more appropriate newsgroup.

In your case, you must have only a single WHERE statement in a select query,
so you should take this into account when building your dynamic sql string.
Try with something like:

CritW = ""

If Me.Option6 Or Me.Option9 Then
CritW = " (((PostJobs.IR_TECH_OJB) = '" & Me.Text3 & "') And
((PostJobs.COMPL_DTE_OJB) = #" & Me.Text8 & "#))"

If Me.Option9 = False Then ' TEXT FIELD
if (CritW <> "") then CritW = CritW & " And "
CritW = CritW & "(((PostJobs.IR_TECH_OJB)='" & Me.Text3 & "')) "
End If

If Me.Option6 = False Then 'DATE FIELD
if (CritW <> "") then CritW = CritW & " And "
CritW = CritW & "(((PostJobs.COMPL_DTE_OJB) = #" & Me.Text8 & "#)) "
End If

If Me.Option13 = True Then 'Number Field
if (CritW <> "") then CritW = CritW & " And "
CritW = CritW & "(((PostJobs.JOB_NO_OJB)= " & Me.Text12 & ")) "
End If
End If

If (CritW <> "") then CritW = " WHERE " &

Me.List0.RowSource = CritS & CritW & CritO

I've removed the delimiters around the number field and also, for the text
field, you should make sure that any embedded quote are doubled:

CritW = CritW & "(((PostJobs.IR_TECH_OJB)='" & Replace (Me.Text3, "'", "''")
& "')) "

It would also be a good idea to display the final result of the query string
in a message box or in the debug window in order to verify that everything
is OK; in particular that there is no missing blank space. Finally, I don't
understand why you have the test for Me.Option13 inside the block for
Me.Option6 and Me.Option9.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I have an access database that links in some SQL tables. There is a form
where end users select options for what they want to display. Once an
option
button is selected the text box associated with it is enabled and they can
enter in thier criteria. I have this working for two but can not get the
third. I am sure it has to do with the format the code I have is as
follows:

Private Sub Command5_Click()
Dim a As String
Dim CritS As String
Dim CritW As String
Dim CritO As String

CritS = "SELECT PostJobs.COMPL_DTE_OJB AS [Comp], PostJobs.ORDER_NO_OJB AS
[Order], PostJobs.JOB_SEQ_NO_OJB, PostJobs.JOB_NO_OJB AS Job,
PostJobs.JOB_TYP_OJB AS Type, PostJobs.IR_TECH_OJB AS Tech,
PostJobs.QC_STATUS, PostJobs.QC_REP, PostJobs.QC_DATE FROM PostJobs "

CritO = "ORDER BY PostJobs.IR_TECH_OJB, PostJobs.COMPL_DTE_OJB; "

If Me.Option6 Or Me.Option9 Then
' CritW = "WHERE (((PostJobs.IR_TECH_OJB) = '" & Me.Text3 & "') And
((PostJobs.COMPL_DTE_OJB) = #" & Me.Text8 & "#))"

' If Me.Option9 = False Then ' TEXT FIELD
' CritW = "WHERE (((PostJobs.IR_TECH_OJB)='" & Me.Text3 & "')) "
' End If

' If Me.Option6 = False Then 'DATE FIELD
' CritW = "WHERE (((PostJobs.COMPL_DTE_OJB) = #" & Me.Text8 & "#)) "
' End If

If Me.Option13 = True Then 'Number Field
CritW = "WHERE (((PostJobs.JOB_NO_OJB)='" & Me.Text12 & "')) "
End If

'End If
Me.List0.RowSource = CritS & CritW & CritO
End Sub

If I use the CritW options for the other 2 and still use Text12 it works,
I
know, or strongly feel, its the format for the number field, but I'll be
buggered if I can find it. It's probably simply and I am over thinking it.

Thanks for the help in advance