Using docmd.SetParameter to pass an string to a report/query (1 Viewer)

pacctono

Member
Local time
Today, 16:05
Joined
Jun 13, 2022
Messages
32
Hello!

I am a kind of a new access user.

I have a report using a query. I have a form that call the report. I am passing parameters from the form to the query throught vba using docmd. setParameter.
Like:
DoCmd.SetParameter "cta", cta
Everything goes well when cta has numbers inside. But give me "execution error 2434..." when it has '*' inside.

Does the '*' char has any special meaning to vba/access?
Is there a way to pass it?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:05
Joined
Oct 29, 2018
Messages
19,209
Hi. Welcome to AWF!

Yes, "*" is used as a wildcard character in Access. If your data has *s in them, you could try enclosing them in your code with square brackets ([*]). Otherwise, consider showing us your VBA code.
 

pacctono

Member
Local time
Today, 16:05
Joined
Jun 13, 2022
Messages
32
I know is a wildcard in the query. The problem is inside the vba:

Private Sub Aceptar_Click()
Dim cta As String, informe As String
Dim fecini As Long, fecfin As Long
cta = Replace(Me.cta.Value, "-", "")
fecini = DateDiff("d", #1/1/1900#, Me.fecini.Value) + 2
fecfin = DateDiff("d", #1/1/1900#, Me.fecfin.Value) + 2
If Mid(cta, 2, 6) = "000000" Then
cta = Mid(cta, 1, 1)
ElseIf Mid(cta, 4, 4) = "0000" Then
cta = Mid(cta, 1, 3)
ElseIf Mid(cta, 6, 2) = "00" Then
cta = Mid(cta, 1, 5)
End If
informe = "reporte_diarios"
DoCmd.Close acReport, informe
DoCmd.SetParameter "cta", cta
DoCmd.SetParameter "fecini", fecini
DoCmd.SetParameter "fecfin", fecfin
DoCmd.OpenReport informe, acViewPreview
End Sub

If I change the line 'DoCmd.SetParameter "cta", cta' to 'DoCmd.SetParameter "cta", cta & "*"', I get the execution time error '2434'

Note: I solved my problem putting '& "*"' directly in the query; but stilll, I would like to know why I am getting that error.
 

sonic8

AWF VIP
Local time
Today, 22:05
Joined
Oct 27, 2015
Messages
620
I would like to know why I am getting that error.
Because the parameter you set with DoCmd.SetParameter is evaluated as an expression. To pass a literal, you must enclose it in delimiters to achieve it being treated as a literal when the expression is evaluated.

Code:
DoCmd.SetParameter "cta", "'" & cta & "*" & "'"

Warning: DoCmd.SetParameter seems to be not supported in the Access Runtime. :-(
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:05
Joined
May 7, 2009
Messages
16,881
you open your Query in design view and change your Criteria
from Equality (=) to (Like):

Like "*" & [cta] & "*"
 

pacctono

Member
Local time
Today, 16:05
Joined
Jun 13, 2022
Messages
32
Because the parameter you set with DoCmd.SetParameter is evaluated as an expression. To pass a literal, you must enclose it in delimiters to achieve it being treated as a literal when the expression is evaluated.

Code:
DoCmd.SetParameter "cta", "'" & cta & "*" & "'"

Warning: DoCmd.SetParameter seems to be not supported in the Access Runtime. :-(
Thanks, it worked! Looks like DoCmd.SetParameter works only with string, using your suggestion or number.
 
Last edited:

Users who are viewing this thread

Top Bottom