Parameter Query with defaults

[jacquid]

How do you set defaults for parameter based queries?

For example a date parameter with a default of "01/09/2000"

 

[kathryn]

How are you currently getting the parameter value to the query?

Kathryn

 

[jacquid]

Either by entering the prompt in the query or by using a form.

 

[kathryn]

Well, if you are using a form, I am going to assume that you have a command button, with a click event to run the query. You can test the value of the text box, and if it is empty, then set it to your default.

Would that work for you?

Kathryn

 

[jacquid]

My VB is limited how would I achive this?

 

[kathryn]

OK, on your form, do you have a button to run the query? If so, could you post the code that the button runs? I can show you how to modify it.

Also, please post the SQL for the query that you want to run. You will be using the parameter to build an SQL statement 'on the fly' and running it from code.



Kathryn

 

[jacquid]

Button code:
Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String

stDocName = "SwitchConfL-0-1-MainExtract-Qry"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click

End Sub

SQL:
SELECT span_ip_cl_acc_hold_trans.cl_number
FROM ...
WHERE ((span_ip_cl_acc_hold_trans.cl_number) Between [Forms]![SwitchConfLInputParams]![txtClientNumberFrom] And [Forms]![SwitchConfLInputParams]![txtClientNumberTo]) And ((span_ip_cl_acc_hold_trans.proc_date)=[Forms]![SwitchConfLInputParams]![txtProcessedDate]));

 

[kathryn]

Sorry, but I have one more question. Your button opens the query, which is unusual. Usually, one would open a form or report based on the query. Are your users editing the query directly?


Anyway, here is the modified code. This is not exact;

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
Dim strSQL as string
Dim strNumFrom as string
Dim strNumTo as string
Dim datProcDate as date
Dim qdf as QueryDef

'SET UP YOUR DEFAULT VALUES
If me![txtClientNumberFrom] ="" then
strNumFrom= YourDefaultValue
else
strNumFrom = me![txtClientNumberFrom]
end if

'SAME LOGIC FOR THE OTHER TWO PARAMETERS.


strSQL = "SELECT span_ip_cl_acc_hold_trans.cl_number
FROM ...
WHERE ((span_ip_cl_acc_hold_trans.cl_number) Between " & strNumFrom & " And " & strNumTo & " And ((span_ip_cl_acc_hold_trans.proc_date)=" & datProcDate & &quot);


set qdf = currentdb.createQueryDef("Extract",strSQL)

DoCmd.OpenQuery "Extract", acNormal, acEdit

Exit_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click

End Sub


I'm not positive that you can open the Extract query without saving the qdf first. This should give you a start though.



Kathryn

 

[jacquid]

I got the idea to call the query from a form from "SAMs Teach Yourself MS access 2000 in 21 days" pg. 448

Thanx for the VB - I will try it.

 

[jacquid]

The code did not work but I found an alternative - place a default on the input box.