Passing values to query parameters

[pledbetter]

Looks like my earlier attempt to post failed. Let's try again. please refer to attachments. I have a query with Date formatted parameters. I created a form with a button with an "OnClick" action that runs the following VBA code. My attempts to use DoComd.SetParameter and DoCmd.OpenQuery apparently does no pass the parameters as expected. When I click on the command button I still get prompts for my parameters. Here is the OnClick code:


Private Sub btnGetLastWeeksData_Click()

Dim BG As Date
Dim ED As Date


Select Case Weekday(Now())
Case 1
BG = Date - 6
ED = Date - 2

Case 2
BG = Date - 7
ED = Date - 3

Case 3
BG = Date - 8
ED = Date - 4

Case 4
BG = Date - 9
ED = Date - 5

Case 5
BG = Date - 10
ED = Date - 6

Case 6
BG = Date - 11
ED = Date - 7

Case 7
BG = Date - 12
ED = Date - 8

End Select

DoCmd.SetParameter "BeginDate", BG
DoCmd.SetParameter "EndDate", ED
DoCmd.OpenQuery "qryCustomDates"




End Sub

[Welshgasman]

Have you actually defined parameters in the query?

[pledbetter]

Yes. It's in my attached document labeled Query.doc And the creation is evident by the prompt from those very same parameters when I click the button

[pledbetter]

Here are the query parameter definitions

[Micron]

Not wrong, but all that Select Case is probably replaceable with
BG = Date - (Weekday(Now())+5)
ED = Date - (Weekday(Now())+1

I've never tried to deal with parameters in that fashion. Prefer to Dim qdef as Parameter and define them in code, or write the sql and pass them as variables even if I have to do something like DLookup first. I find variables simpler.

[pledbetter]

Not wrong, but all that Select Case is probably replaceable with
BG = Date - (Weekday(Now())+5)
ED = Date - (Weekday(Now())+1

Sweet! That's a nice algorithm! I don't understand why it's so difficult to pass a value, properly formatted, to a parameter of an existing query. I'm getting back into VBA after a long hiatus. I certainly have done it before. I don't understand why this code just blinks at me and runs the query but does not pass the parameters.

[Micron]

I'm sure that given the wealth of talent here, it will get figured out, but I have to take a break.
Got stitches in my R ring finger today because I was careless with the table saw and the meds are not doing a whole lot for me.

[ano]

Dim BG As DateDim ED As Date
Dim str As String
BG = Date - 5 - Weekday(Now())
ED = Date - 1 - Weekday(Now())
BG = #1/19/2011#
ED = #3/19/2011#
str = "#" & Format(BG, "mm/dd/yyyy") & "#"
DoCmd.SetParameter "bgp", str
str = "#" & Format(ED, "mm/dd/yyyy") & "#"
DoCmd.SetParameter "edp", str
Debug.Print str
DoCmd.OpenQuery "transactions query"

PARAMETERS [bgp] DateTime, [edp] DateTime;
SELECT [bgp], [edp], Transactions.[TransactionID], Transactions.[TransactionDate], Transactions.[TransactionAmount], Transactions.[CustomerID]
FROM Transactions
WHERE Transactions.TransactionDate Between bgp And edp;
this query works

[moke123]

I'm sure that given the wealth of talent here, it will get figured out, but I have to take a break.
Got stitches in my R ring finger today because I was careless with the table saw and the meds are not doing a whole lot for me.

Ouch! Finally finishing my guitar after all these years?
Here's to a speedy recovery

[Welshgasman]

THis worked for me, but did not initially?

Sub TestParameters()
Dim BD As Date, ED As Date


BD = DateAdd("yyyy", -9, Date)
ED = DateAdd("yyyy", -1, Date)


DoCmd.SetParameter "BeginDate", BD
DoCmd.SetParameter "EndDate", ED
DoCmd.OpenQuery "qryParameters"
End Sub

PARAMETERS BD DateTime, ED DateTime;
SELECT TestTransactions.ID, TestTransactions.TransactionDate
FROM TestTransactions
WHERE (((TestTransactions.TransactionDate) Between [BD] And [ED]));

and it should not have, as I had the paremeter names wrong?
I changed the names and got the prompt. Sometimes I do and sometimes I do not, and it runs fine?

[pledbetter]

Dim BG As DateDim ED As Date
Dim str As String
BG = Date - 5 - Weekday(Now())
ED = Date - 1 - Weekday(Now())
BG = #1/19/2011#
ED = #3/19/2011#
str = "#" & Format(BG, "mm/dd/yyyy") & "#"
DoCmd.SetParameter "bgp", str
str = "#" & Format(ED, "mm/dd/yyyy") & "#"
DoCmd.SetParameter "edp", str
Debug.Print str
DoCmd.OpenQuery "transactions query"

PARAMETERS [bgp] DateTime, [edp] DateTime;
SELECT [bgp], [edp], Transactions.[TransactionID], Transactions.[TransactionDate], Transactions.[TransactionAmount], Transactions.[CustomerID]
FROM Transactions
WHERE Transactions.TransactionDate Between bgp And edp;
this query works

Hey good morning! OK I feel I need to give more information on my project here. I was originally attempting to focus on the syntax of passing a Date formatted object to a query parameter which seems to be ignored, for some reason. But let me expand:

I want to create a query that pulls sales info from "Last Week". I had to calculate the Beginning Date and the End Date, hence the weird Case statement. Micron's cool algorithm will definitely replace that. So the job of figuring out when Monday and Friday of "Last Week" is complete. The query in question has two defined, Date formatted parameters: BeginDate and EndDate. I also created a form that (for now) has a single Command Button. The OnClick Event has the referenced code. I work in a very distracting environment so my variable for BeginDate is "BG" instead of "BD". So my two variables represent "Begin Date" (BG) and "End Date" (ED). So now my questions about your code:

Why the static assignment assignments for BG & ED? Why are you passing strings to a Date formatted parameter? Is that what you do regardless of how the parameter is formatted?

[Welshgasman]

I have just lost my last post???

This works.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.

Sub TestParameters()
Dim BD As Date, ED As Date


BD = DateAdd("yyyy", -9, Date)
ED = DateAdd("yyyy", -3, Date)




DoCmd.SetParameter "BeginDate", Format(BD, strcJetDate)
DoCmd.SetParameter "EndDate", Format(ED, strcJetDate)


DoCmd.OpenQuery "qryParameters"
End Sub

I have never used SetParameters, so I had to experiment.

[ano]

a)Why the static assignment assignments for BG & ED?b) Why are you passing strings to a Date formatted parameter? c)Is that what you do regardless of how the parameter is formatted?

a)static to adjust for my database test
b)the date field did not transfer,after string with the date # convention the query worked
c)not sure what u mean here , u use date variable thats ok and within vba no problems, but seems transfer to sql need string. dont know of other externe transfers have same issue

sorry for the confusion : i used an old database to check my test so i first replace ur select case into a single line for each date and then i overwrite to adjust to my dates,
without the string i did not get output but after string i had result. welshgasman do the same with strcjetdate

if u only want same week as today u not need parameters
WHERE datepart("ww",Transactions.TransactionDate) = datepart("ww",now()); ' is weeknr
i change my where clause to this and added a record in the table and got the new record. meaning no parameters use

[pledbetter]

OK, another dumb question. how do I mark this as "Solved"?

[Micron]

Look for "thread tools" near the top right of the page