Change SQL where based on form controls (1 Viewer)

DublD · Jun 30, 2015

Hi,

First off, I am extremely new to Access programming and I apologise if I sound stupid.

I have a form with a combobox, textbox, a button and a listbox.

Currently the button checks if the textbox is empty and if it is, it runs a SQL query to select data using the combobox value in the where clause, else it selects data using the textbox value in the where clause

I need to be able to type or select values in both controls and use those values in the SQL query as a where.

any help would be appreciated

pbaldy · Jun 30, 2015

You should be able to adapt your code to use both. Test if both have values, and if so use both in your SQL, separated by AND.

DublD · Jun 30, 2015

Hi pbaldy,

Thank you for your quick response. Will you be able to explain what you mean? Here is my current code if that will help:

Code:

Dim rsPG As DAO.Recordset
    
    Dim vRow As Long, vCol As Long
    
    If Not IsNull(Me.itemNumTxt) Then
        'Fetch data from query and copy into temporary array
        Set rsPG = CurrentDb.OpenRecordset("SELECT dbo_vw_ReorderApp.ITEMNO, dbo_vw_ReorderApp.VENDITEMNO," _
        & "dbo_vw_ReorderApp.ITEMDESC, dbo_vw_ReorderApp.VENDNUM, dbo_vw_ReorderApp.VENDNAME, " _
        & "dbo_APVEN.TEXTPHON1 AS [PHONE], dbo_APVEN.TEXTPHON2 AS [FAX], dbo_vw_ReorderApp.STOCKUNIT, dbo_vw_ReorderApp.PICKINGSEQ," _
        & "dbo_vw_ReorderApp.QTYOH AS [ON HAND], dbo_vw_ReorderApp.QTYONPO AS [ON PO], dbo_vw_ReorderApp.RECENTCOST, " _
        & "dbo_vw_ReorderApp.LASTPONUMBER, dbo_vw_ReorderApp.LASTVDCODE, dbo_vw_ReorderApp.LASTVDNAME," _
        & "dbo_vw_ReorderApp.LASTPOCOST, ROUND(dbo_vw_ReorderApp.SOLD365,2) AS [ACTUAL 365 DAYS], ROUND(dbo_vw_ReorderApp.PROJECTED45,2) AS [PROJECTED 45 DAYS]," _
        & "ROUND(dbo_vw_ReorderApp.SOLDCM,2) AS [CURRENT MONTH], ROUND(dbo_vw_ReorderApp.SOLDLM,2) AS [CURRENT MONTH -1], ROUND(dbo_vw_ReorderApp.SOLDLM1,2) AS [CURRENT MONTH -2]," _
        & "ROUND(dbo_vw_ReorderApp.SOLDLM2,2) AS [CURRENT MONTH -3], ROUND(dbo_vw_ReorderApp.SOLDLM3,2) AS [CURRENT MONTH -4] FROM dbo_vw_ReorderApp " _
        & "LEFT JOIN dbo_APVEN ON dbo_vw_ReorderApp.VENDNUM=dbo_APVEN.VENDORID WHERE dbo_vw_ReorderApp.ITEMNO like'" & Me.itemNumTxt.Value & "*';")
    Else
        Set rsPG = CurrentDb.OpenRecordset("SELECT dbo_vw_ReorderApp.ITEMNO, dbo_vw_ReorderApp.VENDITEMNO," _
        & "dbo_vw_ReorderApp.ITEMDESC, dbo_vw_ReorderApp.VENDNUM, dbo_vw_ReorderApp.VENDNAME, " _
        & "dbo_APVEN.TEXTPHON1 AS [PHONE], dbo_APVEN.TEXTPHON2 AS [FAX], dbo_vw_ReorderApp.STOCKUNIT, dbo_vw_ReorderApp.PICKINGSEQ," _
        & "dbo_vw_ReorderApp.QTYOH AS [ON HAND], dbo_vw_ReorderApp.QTYONPO AS [ON PO], dbo_vw_ReorderApp.RECENTCOST, " _
        & "dbo_vw_ReorderApp.LASTPONUMBER, dbo_vw_ReorderApp.LASTVDCODE, dbo_vw_ReorderApp.LASTVDNAME," _
        & "dbo_vw_ReorderApp.LASTPOCOST, ROUND(dbo_vw_ReorderApp.SOLD365,2) AS [ACTUAL 365 DAYS], ROUND(dbo_vw_ReorderApp.PROJECTED45,2) AS [PROJECTED 45 DAYS]," _
        & "ROUND(dbo_vw_ReorderApp.SOLDCM,2) AS [CURRENT MONTH], ROUND(dbo_vw_ReorderApp.SOLDLM,2) AS [CURRENT MONTH -1], ROUND(dbo_vw_ReorderApp.SOLDLM1,2) AS [CURRENT MONTH -2]," _
        & "ROUND(dbo_vw_ReorderApp.SOLDLM2,2) AS [CURRENT MONTH -3], ROUND(dbo_vw_ReorderApp.SOLDLM3,2) AS [CURRENT MONTH -4] FROM dbo_vw_ReorderApp " _
        & "LEFT JOIN dbo_APVEN ON dbo_vw_ReorderApp.VENDNUM=dbo_APVEN.VENDORID WHERE dbo_vw_ReorderApp.VENDNUM='" & Me.cboVendNum.Value & "';")
    End If

DublD · Jun 30, 2015

Oh wow... I just realised now how vague my question was. You're reply was absolutely perfect for my original question pbaldy, however, what I really want to know is:

I need to test both controls, if they are empty throw a message, if only one is empty run the query with the where clause for that control and vice versa. I also need the ability to use both values from both controls in a query combining them with AND.

So I need to be able to use either one of the controls or both to pass values to the SQL query.

Apologies for that.

pbaldy · Jun 30, 2015

I understood your question. You can use ElseIf:

Code:

If IsNull(Me.itemNumTxt) And IsNull(Me.Me.cboVendNum) Then
  'they're both Null, message box
ElseIf Not IsNull(Me.itemNumTxt) And Not IsNull(Me.Me.cboVendNum) Then
  'they're both filled, code for that with AND
ElseIf Not IsNull(Me.itemNumTxt) Then
  'code for item
Else
  'code for vendor
End If

DublD · Jul 1, 2015

Thank you Paul. It's exactly what I needed

pbaldy · Jul 1, 2015

Happy to help and welcome to the site by the way!

Change SQL where based on form controls (1 Viewer)

DublD

New member

pbaldy

Wino Moderator

DublD

New member

DublD

New member

pbaldy

Wino Moderator

DublD

New member

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread