Results 1 to 12 of 12
  1. #1
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70

    Combined Field Criteria VBA

    I have a query that I made using VBA;

    Code:
    Dim strSql As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strCriteria As String
    Dim strFields As String
    
    strFields = "[tblPatient].[PatientID], [tblPatient].[FirstName] & ' ' & [tblPatient].[LastName] AS PatientName, DateOfBirth, [tblPatient].[FacilityID]"
    
    strCriteria = ""
    If Not IsNull(Me.PatientName) Then
    strCriteria = " WHERE [PatientName] Like '*" & [Forms]![frmDataExtraction]![PatientName] & "*'"
    End If
    
    
    strSql = "SELECT " & strFields & "  FROM tblPatient" & strCriteria
    qdf.SQL = strSql
    When running this the query cannot determine what field the PatientName belongs to if there is a value in the textbox "PatientName", but if there is nothing in that text box the query is made with the PatientName field populated.



    What can I do to fix this so that the strCriteria works properly and the query has a field with the proper criteria.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Before the last statement, add this line: Debug.Pring strSQL. Then copy it from the immediate window and paste into a new query window, fix it there and make the same changes in your code.

  3. #3
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    I just tried this one too; where if there is something in PatientID1 I get all the values that have that ex. "1" ill get 0001, 0010, 0011...
    Now that I want between values PatientID1 through PatientID2 it gives me a syntax error; instead of giving me the values 1-5 as I wanted.

    Code:
    If Not IsNull(Me.PatientID1) And IsNull(Me.PatientID2) Then
    If (Len(strCriteria) > 0) Then
    strCriteria = strCriteria & " AND [tblPatient].[PatientID] Like '*" & [Forms]![frmDataExtraction]![PatientID1] & "*'"
    Else
    strCriteria = " WHERE [tblPatient].[PatientID] Like '*" & [Forms]![frmDataExtraction]![PatientID1] & "*'"
    End If
    ElseIf Not IsNull(Me.PatientID1) And Not IsNull(Me.PatientID2) Then
    If (Len(strCriteria) > 0) Then
    strCriteria = strCriteria & " AND NumericPatientID: Val([tblPatient].[PatientID]) Between [Forms]![frmDataExtraction]![PatientID1] And [Forms]![frmDataExtraction]![PatientID2]"
    Else
    strCriteria = " WHERE NumericPatientID: Val([tblPatient].[PatientID]) Between [Forms]![frmDataExtraction]![PatientID1] And [Forms]![frmDataExtraction]![PatientID2]"
    End If
    End If

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is PatientName bound to field? Is this textbox purpose to input filter criteria? If so, it should not be bound to field, otherwise you change value of record.

    Why don't you concatenate the textbox instead of the field?

    Suggest you name textbox different from the field, such as tbxPatID.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    Thank you Aytee111; that helped me fix my first error; I will try it with the next error right now.

    Code:
    SELECT tblPatient.PatientID, [tblPatient].[FirstName] & ' ' & [tblPatient].[LastName] AS PatientName, tblPatient.DateOfBirth, tblPatient.FacilityID
    FROM tblPatient
    WHERE ((([tblPatient].[FirstName] & ' ' & [tblPatient].[LastName]) Like '*s*'));

  6. #6
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    PatientName is not a bound field; if anything its a calculated or just a combined field of FirstName and LastName on tblPatient. (on the search form its unbound)
    The Textboxes are used for the criteria; so I want patient 1 through patient 10: PatientID1 = 1 And PatientID2 = 10
    Using the query below sorta works; PatientID1 = 1 and PatientID2 = 10 but I also get anything with a 1 or 10 in it (110, 111, 210, 211); which I dont see where I am going wrong with that; I change the "[Forms]![frmDataExtraction]![PatientID1]" to 1 and "[Forms]![frmDataExtraction]![PatientID2]" to 10 and it works fine.

    I should also note that the Patient ID looks like 000001MM so Val() turns it into 1. ex. 000100MM = 100

    Code:
    SELECT tblPatient.PatientID, [tblPatient].[FirstName] & ' ' & [tblPatient].[LastName] AS PatientName, tblPatient.DateOfBirth, tblPatient.FacilityID
    FROM tblPatient
    WHERE (((Val([tblPatient].[PatientID])) Between [Forms]![frmDataExtraction]![PatientID1] And [Forms]![frmDataExtraction]![PatientID2]));

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What about "Val([Forms]![frmDataExtraction]![PatientID1]..."?

  8. #8
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    I fixed both queries so thank you for any advice given, I will probably post here again when I need help with the criteria portion of this; I can get up to 3 so far ( I haven't tired more than that).

    Code:
    strCriteria = ""
    If Not IsNull(Me.PatientName) Then
    strCriteria = " WHERE ((([tblPatient].[FirstName] & ' ' & [tblPatient].[LastName]) Like '*" & [Forms]![frmDataExtraction]![PatientName] & "*'))"
    End If
    
    
    
    If Not IsNull(Me.PatientID1) And IsNull(Me.PatientID2) Then
    If (Len(strCriteria) > 0) Then
    strCriteria = strCriteria & " AND [tblPatient].[PatientID] Like '*" & [Forms]![frmDataExtraction]![PatientID1] & "*'"
    Else
    strCriteria = " WHERE [tblPatient].[PatientID] Like '*" & [Forms]![frmDataExtraction]![PatientID1] & "*'"
    End If
    ElseIf Not IsNull(Me.PatientID1) And Not IsNull(Me.PatientID2) Then
    If (Len(strCriteria) > 0) Then
    strCriteria = strCriteria & " AND (Val([tblPatient].[PatientID]) Between Val([Forms]![frmDataExtraction]![PatientID1]) And Val([Forms]![frmDataExtraction]![PatientID2]))"
    Else
    strCriteria = " WHERE (Val([tblPatient].[PatientID]) Between Val([Forms]![frmDataExtraction]![PatientID1]) And Val([Forms]![frmDataExtraction]![PatientID2]))"
    End If
    End If

  9. #9
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    aytee111 I just tried that right before you posted it and it worked; I didn't think I would need to classify 1 as a numerical value, but I couldnt see how to tell the textbox that I want it be a numerical value when entered.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you are doing with this, what you by "3", if you want to explain what you are trying to accomplish we may be able to help further.

    BTW, no need to keep repeating the Forms!Formname - you could use "Me" throughout.

  11. #11
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    Very True I could have used Me. to reference the current form; I just wanted to make sure it was pulling the correct values; and I think I got the more than 3 criteria which I have posted on here before about; but never tried making a query through vba.

    So a technician here would want to look up a Patient Name starting with "J", that is older than 30, and we received a sample in 2018. Before when putting this kind of criteria in a query it wouldnt be able to sift through the data to give me exactly what I wanted (I think it was due to the fact that it didnt know which criteria to look at first). but in VBA all I have to do is add an "AND" after the last criteria and it seems to put the data through properly (as of now)

  12. #12
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    or use a variable
    Dim strPat1 As String

    strPat1 = Me.frmDataExtraction.PatientID1

    ... Like '*" & strPat1 & "*'"...

    I would avoid using ! operator between form/report names and their controls ([frmDataExtraction]![PatientID1]).
    These references are not evaluated at compile time - only at run time. This means if you mistype the control name, it isn't evaluated until code runs. Forms!frmName is correct.
    More info on bang operator: https://stackoverflow.com/questions/...-and-ms-access
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 01-22-2016, 01:12 PM
  2. Replies: 1
    Last Post: 03-28-2012, 03:27 PM
  3. Replies: 1
    Last Post: 07-10-2011, 01:28 PM
  4. Combined "person" field on form
    By Remster in forum Forms
    Replies: 14
    Last Post: 09-15-2010, 10:44 AM
  5. Format options in combined Form field
    By perry in forum Forms
    Replies: 0
    Last Post: 03-06-2009, 04:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums