CODEWITHSUNDEEP
sqlvbams-access
Hadi
Hadi

Reputation: 39

optional parameters on report

i have a table that has 10 field in Microsoft Access, but i want filter on their maximum 5 field i left 5 checkbox and for any once 1 combobox to select 1 to 5 option to filter, in other words dynamic report on 5 filed. my code:

my New code changes:

Dim p1 As Long
Dim p2 As String
Dim p3 As String
Dim p4 As String
Dim p5 As String
p2 = "*"
p3 = "*"
p4 = "*"
p5 = "*"
p1 = 20500101
If (chbeckbox1 = True) Then p1 = combobox1
If (chbeckbox2 = True) Then p2 = combobox2
If (chbeckbox3 = True) Then p3 = combobox3
If (chbeckbox4 = True) Then p4 = combobox4
If (chbeckbox5 = True) Then p5 = combobox5
strCriteria = "([DateCutting]<=" & p1 & ") And [SystemName]= '" & p2 & "' And [CenterName]='" & p3 & "' And [TypeCutting]='" & p4 & "' And [ReporterName]='" & p5 & "'"
task = "select * from Cutting where (" & strCriteria & ")"
DoCmd.ApplyFilter task

define 5 variable and .... but i got no correct result or syntax error what is solution???

Upvotes: 0

Views: 103

Answers (1)

June7
June7

Reputation: 21370

Normally, asterisk (*) is wildcard character in Access. https://support.microsoft.com/en-us/office/access-wildcard-character-reference-af00c501-7972-40ee-8889-e18abaad12d1

Parameters for date/time field need # character as delimiter. "([DateCutting]<=#" & p1 & "#) And

If variable type is not explicitly declared it defaults to Variant. So in your variable declaration only p5 is String type, the other 4 are Variant. Default value for Variant type is empty string.

Your criteria is built such that variables are still referenced in concatenation even if empty (null or empty string). The result can be an invalid criteria string - as in passing an empty string for a date/time field. Even if the criteria works without error, output could be not what is desired.

Instead of passing empty strings, use wildcard (*) with text field parameters and provide an alternate value for date/time field.

Alternatively, conditionally build string so field and its parameter are only used if there is value AND user has selected for it.

Upvotes: 1

Related Questions