Using docmd.SetParameter to pass an string to a report/query

[pacctono]

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]

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]

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]

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.

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

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

 

[arnelgp]

you open your Query in design view and change your Criteria
from Equality (=) to (Like):

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

 

[pacctono]

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.

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.