like "*"&[Forms]![MainScreen].[cmbSound]&"*" not wor 1

[Triacona]

Dear All,
Thanks for a great forum!!!

I have the following problem and any help would be greatly appreciated.

I have a few fields in my query PolConsultationReport

The fields do not always have data...

I have used the following in the criteria of my Sound field.

Like "*" & [Forms]![MainScreen].[cmbSound] & "*"

It only pulls a few records, not all of them.
I have tried different table joins within the query.

The tables that are joined are CustomersMainTable to Representations (One to Many, One customer to many representations)

The field Sound in the query comes from the Representations table.

Is there a way to pick up all information even if the field is blank.
Because I have a few combo boxes on the form and want if they are blank to show all records.

Again any help would be greatly appreciated
Thank you!


Thank you,

Kind regards

Triacona

 

[Andrzejek]

Did you try:

WHERE Sound Like "*" & [Forms]![MainScreen].[cmbSound] & "*"
OR Sound = ""

or

WHERE Sound Like "*" & [Forms]![MainScreen].[cmbSound] & "*"
OR Sound IS NULL

Have fun.

---- Andy

 

[dhookom]

To return blanks (nulls) I would use:

[Sound] & "" Like "*" & [Forms]![MainScreen].[cmbSound] & "*"

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

I will try that !
Thank you!
@Andy if I run into any trouble I will try your SQL
Thanks

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear Duane,

I have tried

[Sound] & "" Like "*" & [Forms]![MainScreen].[cmbSound] & "*"

This doesn't seem to work either , it'll do it for No but not yes, strange!!!

Row Source Type: Value List.
Row source: Yes;No

I have also tried "Yes";"No"

I have no idea why this is not working...
Please help, I am quite stuck..
Thank you!

Thank you,

Kind regards

Triacona

 

[dhookom]

What is the data type of the Sound field? What are the possible values? Is this in VBA or just a SQL statement?


Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

[Sound] & "" Like "*" & [Forms]![MainScreen].[cmbSound] & "*"

Works now!
I had to change a few data types on the table to text, as they were cmbboxes on the table with their own preset values?!

This was setup by someone else than me.

But now since I have changed them to text and only used form cmbboxes, it now works with the above code!
YAY!
Thank you again Duane!
Have a star!



Thank you,

Kind regards

Triacona