open report with 2 conditions (1 Viewer)

scallebe

Registered User.
Local time
Today, 03:07
Joined
Mar 23, 2018
Messages
51
Hi specialists,

I want to open a repport with 2 conditions.

part of the code :

Code:
DoCmd.OpenReport "Alle Dossiers", acViewPreview, , "[NN] =" & NN And "[Datum TA] =#" & [Datum TA] & "#"

Only with the "NN" condition, no problem.

[Datum TA] is a combobox to filter my form based on the date in that combobox.

When I want to open my report access gives me a error 13: Types don't match.

My form is based on a Query and the combox values are from the same Query.

Filtering in my form with that combobox is no problem.

He gives a error when I want to use that combobox value as condition.

What am I doeing wrong?:banghead:

Thanks

Greetz

Pascal
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:07
Joined
May 7, 2009
Messages
19,169
maybe this will help:

DoCmd.OpenReport "Alle Dossiers", acViewPreview, , "[NN] =" & NN And "[Datum TA] =#" & Format(CDate([Datum TA]), "mm/dd/yyyy") & "#"
 

isladogs

MVP / VIP
Local time
Today, 02:07
Joined
Jan 14, 2017
Messages
18,186
You need to reference your form controls not the field names e.g. TxtNN, cboDatum.
Also strongly recommend you don't leave spaces in field or control names.

Is the bound field (usually hidden) of your combo a date field?
If not you need to reference the column number.
As the numbering starts at zero, you would need something like Me.[DatumTA].Column(1) for the second column in your combo

So
Code:
DoCmd.OpenReport "Alle Dossiers", acViewPreview, , "[NN] =" & Me.NN & " And [Datum TA] =#" & Me.[Datum TA].Column(1) & "#"

Or if you rename as suggested, you can omit all []
Code:
DoCmd.OpenReport "AlleDossiers", acViewPreview, , "NN =" & Me.TxtNN & " And DatumTA =#" & Me.cboDatumTA.Column(1) & "#"
 

scallebe

Registered User.
Local time
Today, 03:07
Joined
Mar 23, 2018
Messages
51
hi,
Thanks for reply... :)

#arnelgp : your code didn't work… :eek:

#isladogs : I followed your recommendations, renamed my fields and removed the spaces. I left NN like it was because I had no problem there.

My date is in Column 0 so de column number is not needed (I think)

this is my code :

Code:
DoCmd.OpenReport "Alle Dossiers", acViewPreview, , "NN =" & Me.NN & " And DatumTA =#" & Me.cboDatumTA & "#"

And now I have a empty report.

The strange thing is when I create a new Query and put the 2 criteria in the query fields (refering to my form fields) then I see what I want. The goal is of cource to work with one repport and one Query, filtered with some VBA

Thank you all

Greetz
 

isladogs

MVP / VIP
Local time
Today, 02:07
Joined
Jan 14, 2017
Messages
18,186
OK. No type mismatch errors so NN is a number field and DatumTA is a date field.
Next thing to check are the control names on the form are definitely NN and cboDatumTA. If not either rename them or modify the code line.
Also check that the bound column of the combo is the DatumTA field.
If not, you do need to use .Column(0)

FWIW you forgot to rename your report without spaces.

EDIT If your default date format isn't mm/dd/yyyy then you need to format your sql string as arnelgp suggested

Code:
DoCmd.OpenReport "AlleDossiers", acViewPreview, , "NN =" & Me.TxtNN & " And DatumTA =#" & Format(Me.cboDatumTA,"mm/dd/yyyy") & "#"
 
Last edited:

scallebe

Registered User.
Local time
Today, 03:07
Joined
Mar 23, 2018
Messages
51
#arnelgp and #isladogs,

The combination of your suggestions did it... Finally :rolleyes:

This is my final working code :

Code:
DoCmd.OpenReport "AlleDossiers", acViewPreview, , "NN =" & Me.NN & " And DatumTA =#" & Format(CDate([DatumTA]), "mm/dd/yyyy") & "#" '=#" & Me.cboDatumTA.Column(0) & "#"

Thanks also for your tips, sometimes I have the bad habit to work with spaces, format my field names in a bad way, (cbo…, txt…,....)

I still have so much to learn. :cool:

Thanks, and enjoy the rest of the weekend.
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:07
Joined
Sep 21, 2011
Messages
14,048
That actually works?:confused:

Code:
DoCmd.OpenReport "AlleDossiers", acViewPreview, , "NN =" & Me.NN & " And DatumTA =#" & Format(CDate([DatumTA]), "mm/dd/yyyy") & "#" '=#" & Me.cboDatumTA.Column(0) & "#"
#arnelgp and #isladogs,

The combination of your suggestions did it... Finally :rolleyes:

This is my final working code :

Code:
DoCmd.OpenReport "AlleDossiers", acViewPreview, , "NN =" & Me.NN & " And DatumTA =#" & Format(CDate([DatumTA]), "mm/dd/yyyy") & "#" '=#" & Me.cboDatumTA.Column(0) & "#"

Thanks also for your tips, sometimes I have the bad habit to work with spaces, format my field names in a bad way, (cbo…, txt…,....)

I still have so much to learn. :cool:

Thanks, and enjoy the rest of the weekend.
 

isladogs

MVP / VIP
Local time
Today, 02:07
Joined
Jan 14, 2017
Messages
18,186
As Gasman indicated that cannot work
Hopefully you just copied and pasted incorrectly in your last answer.

My previous answer should have worked...with the possible addition of .Column(0) if ne essary
 

scallebe

Registered User.
Local time
Today, 03:07
Joined
Mar 23, 2018
Messages
51
My mistake… :D

This is the working code :

Code:
DoCmd.OpenReport "AlleDossiers", acViewPreview, , "NN =" & Me.NN & " And DatumTA =#" & Format(CDate([DatumTA]), "mm/dd/yyyy") & "#"

There is no reference to the combobox anymore with this code … correct?


Greetz

Pascal
 

isladogs

MVP / VIP
Local time
Today, 02:07
Joined
Jan 14, 2017
Messages
18,186
Not correct. As previously stated, always reference the form controls not the fields
 

Users who are viewing this thread

Top Bottom