Please help with a conditional statement for query by form method

[Sharky II]

Hi there

I'm trying to construct something very similar to the conditional i made below:

If Me![SrchForDebt] = "all" Then
    where = where & " AND [Expr1] NOT like '" + "£0.00" + "'"
Else
    If Left(Me![SrchForDebt], 1) = "*" Or Right(Me![SrchForDebt], 1) = "*" Then
        where = where & " AND [Expr1] like '" + Me![SrchForDebt] + "'"
    Else
        where = where & " AND [Expr1]= '" + Me![SrchForDebt] + "'"
    End If
End If

It's basically saying if the field says "all" then bring up everyone who doesn't have £0.00 in that field, else do some other stuff.

I want it to bring up everyone apart from people who owe money, so if it says £0.00 i want it omitted, and also if there is no value in there, i want it omitted - so:

if field = "all" then
bring up everyone who doesn't have £0.00 OR null
else, do some other stuff

actually, can i just use nul??

Sorry for making a meal of this, hope someone can decipher what i'm trying to ask!

 

[scottn]

You can test for null values in Access SQL like this:

... WHERE FieldName IS NULL

 

[Sharky II]

hi, i just can't get the syntax correct

what i'm trying to say is:

If Me![SrchForDebt] = "all" Then
where = where & " AND [Expr1] NOT like '" + "£0.00" + AND [Expr1] IS NOT NULL "'"
Else..... etc

where have i gone wrong guys?

Thanks

 

[ReAn]

where = where & " AND [Expr1] NOT like '£0.00' AND [Expr1] IS NOT NULL"

 

[Sharky II]

hi!

thanks for the reply. This does work, but doens't give me the result i want.

Basically, i want to select all records where there is either a value greater/other than £0.00 but also there HAS TO BE A VALUE there (ie all records come up other than the people who have £0.00 as they are fully paid off).

At the moment this brings up everyone apart from those paid off, including those people who have absolutely nothing in that field. Surely this should be equivalent to NULL?

Is there anyway i can get rid of those people from teh query and only show the people who have a value other than £0.00 in the field?

Thanks a lot guys

Edd

 

[ReAn]

That depends, if you are using a text field (which mind you is not smart for this) then it could be a space or something in there.

You should be using the currency type, and set the currency to pounds (this is pounds correct?)

Then, it should come up fine.

Otherwise here's my suggestion for a text field:

SQL = "SELECT *, Len(Trim([Expr1])) AS LenOfExpr1 FROM tblWhateverItsNameIs WHERE [LenOfExpr1] > 0 AND [Expr1] Not Like '£0.00'"

The Jet Engine should handle the rest.

 

[Sharky II]

hi

the thing is i wanna bring up all people that owe money - if i set it to currency then i have to enter an actual figure. This way i've set it so that you can enter the word 'all' and it will bring up who owes you money.

I've tried to use the length function as you gave but i can't get it to work.

thing is, it is bring up all 2000 customers even though only about 15 people should come up as owing money, as most the field for nearly all of these people is blank, SOME have £0.00 who have 'paid' and only another handful actually owe money. I know i didn't put a space for all 2000 customers! Maybe as the other fields are defined as currency it's taking it as if it has a £ in there?

 

[Sharky II]

where exactly would i put that Len expression?

something wierd is happening. when i enter £0.00 i get no results. If i wenter £ i get no results. If i enter £0.00 i get 2000 or so results. Something is getting confused with the format of currency/strings etc

I think it's me that's confused actually!

 

[Rich]

Why not just use a query and put <>0 in the currency field's criteria?

 

[ReAn]

Make a new DB, import the table structure of the table you're using, then post that here, that will help me help you. Might wanna hurry, i go home in one hour.

 

[ReAn]

Well, goin home... sorry...

 

[Sharky II]

sorry, was dinner time for me mate

anyone there

what do i use to delimit a currency data type... like ' for string, # for date, what for currency?

thanks

 

[Sharky II]

i've changed tactics, what i'm trying to do now is have a checkbox for all people who have debt. Should be simpler?

I just did:

If Me![SrchForDebts] = -1 Then
where = where & " AND [Expr1] > 0"
End If

And it seems to work!

 

[scottn]

well, it sounds like you might have a work-around figured out, but if you still want to figure out your original problem, i agree that it would be helpful if you could post your table structure.

were you saying earlier that you are using a string field (not a numeric field) in your table? it would be better to use a numeric field. in the table design view, set the default value for that field to 0 and "required" to yes -- then, when you make a new record, the 0 will automatically be populated for that field, indicating that no debt is owed (which is true for any new record, i assume), and circumventing the issue of null values. you're using code to assemble your SQL statement, so there's no reason why you can't write that code to allow users to enter "all" and turn it into a statement like "WHERE debt > 0", even if your table design uses a numeric field.