Expression not working

[somedeadguy]

This is an address field that I am parsing out to get the street number separate from the street in a query.

Here is my expression:

DoNum: Switch(IsNull([dbo_Daily_Students]![DOSTRT]),'No Address',Len([dbo_Daily_Students]![DOSTRT])>=1,Left([dbo_Daily_Students]![DOSTRT],InStr([dbo_Daily_Students]![DOSTRT],' ')))

This results in an #Error, however if I just take the instr() out it works fine, like:

DoNum: Switch(IsNull([dbo_Daily_Students]![DOSTRT]),'No Address',Len([dbo_Daily_Students]![DOSTRT])>=1,Left([dbo_Daily_Students]![DOSTRT],5)))

This results in no errors.....

I'm confused.

 

[ajetrumpet]

readable mode:

DoNum: Switch(IsNull([dbo_Daily_Students]![DOSTRT]),
   'No Address',Len([dbo_Daily_Students]![DOSTRT])>=1,
      Left([dbo_Daily_Students]![DOSTRT],
   InStr([dbo_Daily_Students]![DOSTRT],' ')))

is that a SPACE that you're looking for? If Instr() results in 0, then you're picking the 0 characters on the left of the value. that may be the problem. obviously it would be an error. just spitballing.

DoNum: Switch(IsNull([dbo_Daily_Students]![DOSTRT]),
   'No Address',Len([dbo_Daily_Students]![DOSTRT])>=1,
      Left([dbo_Daily_Students]![DOSTRT],5)))

 

[somedeadguy]

Thanks! How'd you do that? use quote?

 

[ajetrumpet]

how did i do WHAT ?

 

[somedeadguy]

make it "readable?"

 

[ajetrumpet]

the CODE frames on this site are only so LONG, you have to press ENTER to space the code out from line to line. that's how

all code is written in tabulated form anyway. just out of curtosy, you should always post it like I did so people can read it.