Solved user input parameters for sql queries in access (1 Viewer)

murray83

Games Collector
Local time
Today, 13:27
Joined
Mar 31, 2017
Messages
728
Hi trying to pull just certain data using a SQL pass through query so the table isn't stored within the database but am able to query it and pull data

and when trying to pull data with this

Code:
SELECT 

Reference_Id,  Sku_Id, Code

FROM Inventory_Transaction

WHERE Code = 'Receipt' AND (((Reference_Id)=[Which Pre Advice]));

but it faults says missing right parenthesis

have tried google and found this site on stack over flow but its for dates and im wanting a string of 8 numbers inputted

https://stackoverflow.com/questions/13538305/how-to-prompt-user-input-parameters-for-sql-queries-in-access-2010
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:27
Joined
Oct 29, 2018
Messages
21,358
Hi. If you're using a passthrough query, then you'll have to modify its SQL statement to resolve the parameter before running/executing it. You can do this using a QueryDef object.
 

murray83

Games Collector
Local time
Today, 13:27
Joined
Mar 31, 2017
Messages
728
Hi. If you're using a passthrough query, then you'll have to modify its SQL statement to resolve the parameter before running/executing it. You can do this using a QueryDef object.

thanks will check that out ne chance of example
 

murray83

Games Collector
Local time
Today, 13:27
Joined
Mar 31, 2017
Messages
728
well have come back to this and have made some progress but now get this

so the code works as just pure SQL, with the data hardcoded but i need/like to be able to input different SKU's

so in Pure SQL Code and Pure SQL Output is just the passthrough code working 100% ( a happy me )

but then after @theDBguy advice i checked out the link to allow user inputs and so have this code on the run query command button on my one form

Code:
Private Sub cmdJDAMoves_Click()

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Sku_Id, From_Loc_Id, Final_Loc_Id, Tag_Id, cast(Dstamp as date) as [Date] FROM Inventory_Transaction" & _
    "WHERE Sku_Id = " & [Forms]![Form1]![txtSKU]

    
  Set dbCurr = CurrentDb()
  Set qdfCurr = dbCurr.QueryDefs("Q_JDAMoves2")
  qdfCurr.SQL = strSQL



DoCmd.OpenQuery "Q_JDAMoves2"

End Sub

i have added the docmd.open as i think it should be there

but all it does is shown in pictures SQL Code 2 and says an error as shown in error pic, any help would be great

cheers one and all, and if your in the UK watch out for the windy wind
 

Attachments

  • Pure SQL Code.png
    Pure SQL Code.png
    9.1 KB · Views: 169
  • Pure SQL Output.png
    Pure SQL Output.png
    14 KB · Views: 295
  • SQL Code 2.png
    SQL Code 2.png
    4.1 KB · Views: 282
  • error.png
    error.png
    18 KB · Views: 158

theDBguy

I’m here to help
Staff member
Local time
Today, 06:27
Joined
Oct 29, 2018
Messages
21,358
well have come back to this and have made some progress but now get this

so the code works as just pure SQL, with the data hardcoded but i need/like to be able to input different SKU's

so in Pure SQL Code and Pure SQL Output is just the passthrough code working 100% ( a happy me )

but then after @theDBguy advice i checked out the link to allow user inputs and so have this code on the run query command button on my one form

Code:
Private Sub cmdJDAMoves_Click()

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Sku_Id, From_Loc_Id, Final_Loc_Id, Tag_Id, cast(Dstamp as date) as [Date] FROM Inventory_Transaction" & _
    "WHERE Sku_Id = " & [Forms]![Form1]![txtSKU]

 
  Set dbCurr = CurrentDb()
  Set qdfCurr = dbCurr.QueryDefs("Q_JDAMoves2")
  qdfCurr.SQL = strSQL



DoCmd.OpenQuery "Q_JDAMoves2"

End Sub

i have added the docmd.open as i think it should be there

but all it does is shown in pictures SQL Code 2 and says an error as shown in error pic, any help would be great

cheers one and all, and if your in the UK watch out for the windy wind
Hi. I looked at your third image, and I noticed your words are bunched up together without spaces, and you didn't have a delimiter for your SKU value. Maybe try it this way?

Code:
strSQL = "SELECT Sku_Id, From_Loc_Id, Final_Loc_Id, Tag_Id, cast(Dstamp as date) as [Date] FROM Inventory_Transaction" & _
    " WHERE Sku_Id = '" & [Forms]![Form1]![txtSKU] & "'"
Hope that helps...
 

murray83

Games Collector
Local time
Today, 13:27
Joined
Mar 31, 2017
Messages
728
Hi. I looked at your third image, and I noticed your words are bunched up together without spaces, and you didn't have a delimiter for your SKU value. Maybe try it this way?

Code:
strSQL = "SELECT Sku_Id, From_Loc_Id, Final_Loc_Id, Tag_Id, cast(Dstamp as date) as [Date] FROM Inventory_Transaction" & _
    " WHERE Sku_Id = '" & [Forms]![Form1]![txtSKU] & "'"
Hope that helps...

Ok that sort of helps but i have figured out whats wrong, but not show how to code it

when i click the button which then does all the SQL bits, it isn't putting "Date" but Date missing the speech marks and tried '"Date"' and then complained at me and also its missing off the ; at the end of the query

PS EDIT

OK i fixed the missing ; with another & ";"like so

Code:
 " WHERE Sku_Id = '" & [Forms]![Form1]![txtSKU] & "'" & ";"
 

Attachments

  • correct.png
    correct.png
    4 KB · Views: 295
  • wrong.png
    wrong.png
    3.9 KB · Views: 244
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 13:27
Joined
Sep 21, 2011
Messages
14,052
Date is a reserved word?

Use something else?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:27
Joined
Oct 29, 2018
Messages
21,358
Ok that sort of helps but i have figured out whats wrong, but not show how to code it

when i click the button which then does all the SQL bits, it isn't putting "Date" but Date missing the speech marks and tried '"Date"' and then complained at me and also its missing off the ; at the end of the query

PS EDIT

OK i fixed the missing ; with another & ";"like so

Code:
 " WHERE Sku_Id = '" & [Forms]![Form1]![txtSKU] & "'" & ";"
So, are you all good now?
 

Users who are viewing this thread

Top Bottom