Ignore blanks and <0 criteria

[jset818]

Hi everybody, I'm new to the forums. I recently got a job as a data coordinator for a travel company. I have never used Access in my life prior to this job. I have a very newbie question that to you guys the solution is probably very obvious but I can't seem to get it to pull the right data. So I have all these fields with the <0 criteria, which worked for awhile because all we wanted was to pull negative values. But recently, the table we been using has alot of blank values in the first couple rows. This prevents me from running the query then, it would say something like "Datatype Mismatch".



I thought about just changing the criteria to Not like Null and <0 but every time I would try to make any changes to the criteria field, it would add quotation marks around 0. Like so.

I'm think this occurs because the query was made in Access 2003 and I'm using Access 2010. They implemented the auto-complete feature in Access 2010. Sure, I can run the query but it's not pulling the correct data. Sometimes it doesn't pull those fields with the negative values. Any help is appreciated.

Edit: What I want is the query to ignore the blanks and only pull the negative values.

[ranman256]

dont use LIKE
try: IS NOT NULL and <0

[jset818]

dont use LIKE
try: IS NOT NULL and <0

Yeah, it's still putting quotes around my 0. Making it IS NOT NULL <"0". Is <"0" the same as <0?

[ranman256]

no, not same
That usu means the field is a text. Is it text?

But its
IS NOT NULL AND <0

[June7]

Looks like pulling records from a query Allergen_Master_Query. Why not directly from table? Show the SQL statement of Allergen_Master_Query.

[jset818]

no, not same
That usu means the field is a text. Is it text?

But its
IS NOT NULL AND <0

In the Allergan Master Query Excel file, those fields are formatted as General. I wonder if I change the format to Number, maybe that will stop it from adding quotes around my 0.

By the way, the table is called Allergan Master Query. It's not really a query. Just an excel file.

[June7]

So this is a linked spreadsheet? Setting cell format might help but don't bet on it. Can only try.

[jset818]

Okay, I was able to solve the issue by reformatting the appropriate fields in the excel file to Number format (it was General before). The quotes are gone now. I don't even need to ignore the blanks, it does it on its own. Thanks everyone.