something wrong with my code

icemonster

Registered User.
Local time
Yesterday, 20:23
Joined
Jan 30, 2010
Messages
502
so this code seemed to work earlier now it wont. for some reason, it only works when i try to filter the date, but won't work with the text strings, any thing wrong with it?

PHP:
Function startReqList()
    strStartSql1 = "SELECT qry_LABREQUEST4.LABREQUISITIONID, qry_LABREQUEST4.FLABREQUESTID, qry_LABREQUEST4.LRCREATEDONDATE, " _
                 & "qry_LABREQUEST4.CAGENCYNAME , qry_LABREQUEST4.PATIENT, qry_LABREQUEST4.LREQSERVICEDATE, qry_LABREQUEST4.LABREQSTATUS3, " _
                 & "qry_LABREQUEST4.LREQCLIENTID, qry_LABREQUEST4.LREQPATIENTID, qry_LABREQUEST4.LREQSERVICETIME, qry_LABREQUEST4.LREQFASTING, " _
                 & "qry_LABREQUEST4.LRIDC9DIAGNOSIS, qry_LABREQUEST4.LREMPLOYEEID, qry_LABREQUEST4.LRADDITIONALTEST, qry_LABREQUEST4.LRSTATUS, " _
                 & "qry_LABREQUEST4.LRCREATEDBYUSER, qry_LABREQUEST4.LROTHERTESTS, qry_LABREQUEST4.CPHONE1, qry_LABREQUEST4.CPHONE2, " _
                 & "qry_LABREQUEST4.CFAX1, qry_LABREQUEST4.CFAX2, qry_LABREQUEST4.CSTREET1, qry_LABREQUEST4.CSTREET2, qry_LABREQUEST4.CCITY, " _
                 & "qry_LABREQUEST4.CZIPCODE, qry_LABREQUEST4.CSTATE, qry_LABREQUEST4.CEMAIL1, qry_LABREQUEST4.CEMAIL2, qry_LABREQUEST4.CCONTACT1, " _
                 & "qry_LABREQUEST4.CCONTACT2, qry_LABREQUEST4.CADDEDBYID, qry_LABREQUEST4.CADDEDBYUSER, qry_LABREQUEST4.CADDEDONDATE, qry_LABREQUEST4.CADDEDONTIME, " _
                 & "qry_LABREQUEST4.PATIENTID, qry_LABREQUEST4.PLASTNAME, qry_LABREQUEST4.PFIRSTNAME, qry_LABREQUEST4.PPHONE1 , qry_LABREQUEST4.PPHONE2, " _
                 & "qry_LABREQUEST4.PSTREET1, qry_LABREQUEST4.PSTREET2, qry_LABREQUEST4.PCITY, qry_LABREQUEST4.PSTATE, qry_LABREQUEST4.PZIPCODE , " _
                 & "qry_LABREQUEST4.PSEX, qry_LABREQUEST4.PDOB, qry_LABREQUEST4.PHHA, qry_LABREQUEST4.PSSN, qry_LABREQUEST4.PADDEDBY, " _
                 & "qry_LABREQUEST4.PADDEDONDATE, qry_LABREQUEST4.PADDEDONTIME, qry_LABREQUEST4.BILLINGID, qry_LABREQUEST4.BPATIENTID, " _
                 & "qry_LABREQUEST4.BBILLTYPEID, qry_LABREQUEST4.BIDNO, qry_LABREQUEST4.BMEDICARENO, qry_LABREQUEST4.BOTHER, " _
                 & "qry_LABREQUEST4.BILLTOSELECT, qry_LABREQUEST4.BGROUPNO, qry_LABREQUEST4.PFONE1, qry_LABREQUEST4.PFONE2, qry_LABREQUEST4.PCOMPADDRESS, " _
                 & "qry_LABREQUEST4.CFONE1, qry_LABREQUEST4.CFONE2, qry_LABREQUEST4.CCOMPADDRESS, qry_LABREQUEST4.LRFASTING, qry_LABREQUEST4.CFFAX1, " _
                 & "qry_LABREQUEST4.CFFAX2, qry_LABREQUEST4.LABREQTIMEDATE, qry_LABREQUEST4.CFONE12, qry_LABREQUEST4.CFAX12, qry_LABREQUEST4.PFONE12, " _
                 & "qry_LABREQUEST4.LRIDCID, qry_LABREQUEST4.LREQDOCTORID, qry_LABREQUEST4.DOCTOR, qry_LABREQUEST4.DRNPI, qry_LABREQUEST4.LRCREATEDBYID, " _
                 & "qry_LABREQUEST4.LRRESCHEDULED, qry_LABREQUEST4.LRRESCHEDDATE, qry_LABREQUEST4.LRVERIFIED, qry_LABREQUEST4.LABREQSTATUS1, " _
                 & "qry_LABREQUEST4.LABREQSTATUS2, qry_LABREQUEST4.LABREQATTACHID, qry_LABREQUEST4.LRCREATEDONTIME FROM qry_LABREQUEST4 "
                 
    
If Not IsNull(Me.txtLabReqID1) Then
        strWhereSql1 = "WHERE qry_LABREQUEST4.LABREQUISITIONID Like '*" & Me.txtLabReqID1 & "*'"
    Else
        strWhereSql1 = ""
End If

If Not IsNull(Me.cboLabStatus1) Then
    If strWhereSql1 = "" Then
        strWhereSql1 = "WHERE qry_LABREQUEST4.LABREQSTATUS3 Like '*" & Me.cboLabStatus1 & "*'"
    Else
        strWhereSql1 = strWhereSql1 & "AND qry_LABREQUEST4.LABREQSTATUS3 Like '*" & Me.cboLabStatus1 & "*'"
    End If
End If

If Not IsNull(Me.txtHHA1) Then
    If strWhereSql1 = "" Then
        strWhereSql1 = "WHERE qry_LABREQUEST4.CAGENCYNAME Like '*" & Me.txtHHA1 & "*'"
    Else
        strWhereSql1 = strWhereSql1 & "AND qry_LABREQUEST4.CAGENCYNAME Like '*" & Me.txtHHA1 & "*'"
    End If
End If

If Not IsNull(Me.txtPatient1) Then
    If strWhereSql1 = "" Then
        strWhereSql1 = "WHERE qry_LABREQUEST4.PATIENT Like '*" & Me.txtPatient1 & "*'"
    Else
        strWhereSql1 = strWhereSql1 & "AND qry_LABREQUEST4.PATIENT Like '*" & Me.txtPatient1 & "*'"
    End If
End If

If Not IsNull(Me.txtRDFrom) And Not IsNull(Me.txtRDTo) Then
    dtStartDate1 = Me.txtRDFrom
    dtEndDate1 = Me.txtRDTo
    If strWhereSql1 = "" Then
        strWhereSql1 = "WHERE qry_LABREQUEST4.LRCREATEDONDATE Between #" & dtStartDate1 & "# " _
                    & "And #" & dtEndDate1 & "# "
    Else
        strWhereSql1 = strWhereSql1 & "AND qry_LABREQUEST4.LRCREATEDONDATE Between #" & dtStartDate1 & "# " _
                    & "And #" & dtEndDate1 & "# "
    End If
End If


If Not IsNull(Me.txtSDFrom) And Not IsNull(Me.txtSDTo) Then
    dtStartDate2 = Me.txtSDFrom
    dtEndDate2 = Me.txtSDTo
    If strWhereSql1 = "" Then
        strWhereSql1 = "WHERE qry_LABREQUEST4.LREQSERVICEDATE Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    Else
        strWhereSql1 = strWhereSql1 & "AND qry_LABREQUEST4.LREQSERVICEDATE Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    End If
End If

strSortOrderSql1 = " ORDER BY qry_LABREQUEST4.LRCREATEDONDATE DESC;"

strSQL1 = strStartSql1 & strWhereSql1 & strSortOrderSql1
With Me.lstREQUISITION
    .RowSource = strSQL1
    .Value = Null
    
End With
End Function
 
Step one of debugging this sort of thing:

Put
debug.print strSQL1

into your code after the construction of the string is finished. Run the code as usual and inspect the string in the Immediate Window.

If still no clue, paste it into the query designer's SQLview and see what it says there. Make it run in the query designer and then in the SQLview you'll know what it's supposed to look like.
 
You should be able to remove the reference to the query: qry_LABREQUEST4. Just leaving the field names. The SQL statement should still work OK and your code should be easier to read!
 
Try
Code:
[FONT=Courier New][COLOR=#dd0000]Like " & Chr(34) & "*" & Chr(34)[/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000bb]Me[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]txtPatient1 [/COLOR][COLOR=#007700]& Chr(34) & [/COLOR][COLOR=#dd0000]"*" & Chr(34) & "[/COLOR][/FONT]
May work:confused:
 
Uncle Gizmo has told a way of reducing the lines of code in your sql statement, so I recommend you do that. You've also gotten some good advice from other posters too.

Your main problem is there's no space before the "AND. There should be a space there (i.e. between those highlighted parts. What spikepl advised would have allowed you to spot this.

Also, when you're testing dates, don't use IsNull() use IsDate(). IsDate() will return False if it encounters Null too. Finally, you don't seem to be testing the dates individually. What if one textbox is empty and the other isn't?
 
And one other thing: many posters here concoct lengthy SQL-statements and get surprised when the thing does not do what it's supposed to do. Debugging is an unproductive activity that ought to be minimized. How?

  1. Build you SQL step by step, and check that each step works. If it does, add one more complexity to your sql and check again. If that fails, then you know straight away where to focus.
  2. Also, many seem to overlook the fact that they possess a built-in SQL syntax checker/designer in the query designer. You can build the query there, with some fixed values, copy the SQL statement into a string in your code, and then deconstruct the string, replacing the fixed values with variable names one at a time.
But if still clueless:

  • Reverse the process: comment parts of the SQl out. If that works, then comment a bit back in. If not, comment another bit out. You can zoom in on the error in no time.
 
so did the debug.print strSQL, what i have noticed is though i keep getting ALike instead of just Like, cause i believe i checked the SQL compatibility with this project, any ideas how to write ALike in VBA?
 
i already know what doesn't work, it seems that the line
PHP:
If Not IsNull(Me.txtHHA) Then
    If strWhereSql1 = "" Then
        strWhereSql1 = "WHERE CAGENCYNAME Like '*" & Me.txtHHA & "*'"
    Else
        strWhereSql1 = strWhereSql1 & "AND CAGENCYNAME Like '*" & Me.txtHHA & "*'"
    End If

End If

If Not IsNull(Me.txtPatient1) Then
    If strWhereSql1 = "" Then
        strWhereSql1 = "WHERE qry_LABREQUEST4.PATIENT LIKE '*" & Me.txtPatient1 & "*'"
    Else
        strWhereSql1 = strWhereSql1 & "AND qry_LABREQUEST4.PATIENT LIKE '*" & Me.txtPatient1 & "*'"
    End If
End If

doesn't work, everything works fine. the date, even the combo for the status. i find it extremely odd because i have used this code on a previous project and it works fine, the only thing different i did this time was i enabled the option to have a compatibility with the sql.
 
Perhaps you want to re-read my post, second paragraph.
 
even if i put a space, it still returns nothing. i already tried the step by step and that's how i figured it out yet still can't seem to find the reason why it's broken.
 
When you're given suggestions, you should always report back whether it was successful or not so we know the next action steps to take.

After adding the spaces, you have fixed your syntax problem now the problem is records not being returned. Can you Debug.Print your sql and paste it here for us to see.
 

Users who are viewing this thread

Back
Top Bottom