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)
"Dan" <(E-Mail Removed)> wrote in message
news:CE7628F8-8A4D-47DE-8FE5-(E-Mail Removed)...
>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
|
Similar Threads
