Simple function giging me a syntax error, please help!

[Gina Whipp]

Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression '([szMileageFrom]=> 4.5
And ([szMileageTo] = < 4.5) ' ", highlighting the DLookup line. Any help
would be appreciated.

 

[Dirk Goldgar]

In

Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in
VBA, but not in queries (and DLookup builds a query behind the scenes).
Use ">=" and "<=" instead.

 

[Gina Whipp]

Dirk,

Thanks!!! That got rid of the error message but it's still not showing me
35.00. What I am trying to do is take the distance between a and b and if 0
to 50 it's 35.00, if 51 to 999 then it's 75.00. Am I going about this the
correct way or am I missing something?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

In

Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in VBA,
but not in queries (and DLookup builds a query behind the scenes). Use
">=" and "<=" instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

What are you getting instead of 35.00? Does that value exist in the table?

Incidentally, your function is declared as being Currency, but DLookup will
return a Null value if you pass it a value without a corresponding row in
the table, which will cause an error.

To avoid the error, you should use

ServiceZone = Nz(DLookup("szChargeAmount", "tblServiceZones", Crit), 0.0)

to provide a default value.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dirk,

Thanks!!! That got rid of the error message but it's still not showing me
35.00. What I am trying to do is take the distance between a and b and if
0 to 50 it's 35.00, if 51 to 999 then it's 75.00. Am I going about this
the correct way or am I missing something?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

In

Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in VBA,
but not in queries (and DLookup builds a query behind the scenes). Use
">=" and "<=" instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Gina Whipp]

Douglas,

Oops, forgot to say... I am getting a 0.

My table is: szMileageFrom szMileageTo szServiceCharge
0 50 35.00
51 999 75.00

So what I want is if the mileage falls between 0 and 50 to see 35.00 and 51
to 999 to see 75.00. By the way I did add the Nz function because I
realized if my mileage is null it does create an error.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

What are you getting instead of 35.00? Does that value exist in the table?

Incidentally, your function is declared as being Currency, but DLookup
will return a Null value if you pass it a value without a corresponding
row in the table, which will cause an error.

To avoid the error, you should use

ServiceZone = Nz(DLookup("szChargeAmount", "tblServiceZones", Crit), 0.0)

to provide a default value.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dirk,

Thanks!!! That got rid of the error message but it's still not showing
me 35.00. What I am trying to do is take the distance between a and b
and if 0 to 50 it's 35.00, if 51 to 999 then it's 75.00. Am I going
about this the correct way or am I missing something?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

In Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in
VBA, but not in queries (and DLookup builds a query behind the scenes).
Use ">=" and "<=" instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[George Nicholson]

What is it showing you? Are [szMileageFrom] and [szMileageFrom] numeric
fields, or text? (Assuming Mileage is numeric)
Seeing the record in tblServiceZones that you expect to 'hit' when Mileage =
4.5 would help, in case there's a logic issue.

HTH,

Dirk,

Thanks!!! That got rid of the error message but it's still not showing me
35.00. What I am trying to do is take the distance between a and b and if
0 to 50 it's 35.00, if 51 to 999 then it's 75.00. Am I going about this
the correct way or am I missing something?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

In

Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in VBA,
but not in queries (and DLookup builds a query behind the scenes). Use
">=" and "<=" instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Gina Whipp]

George,

The whole point is 4.5 is between 0 and 50 there is no record, it's looking
between 2 values, if the mileage is between those two show me the service
charge. Simple logic I think, it's just not working.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

What is it showing you? Are [szMileageFrom] and [szMileageFrom] numeric
fields, or text? (Assuming Mileage is numeric)
Seeing the record in tblServiceZones that you expect to 'hit' when Mileage
= 4.5 would help, in case there's a logic issue.

HTH,

Dirk,

Thanks!!! That got rid of the error message but it's still not showing
me 35.00. What I am trying to do is take the distance between a and b
and if 0 to 50 it's 35.00, if 51 to 999 then it's 75.00. Am I going
about this the correct way or am I missing something?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

In Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in
VBA, but not in queries (and DLookup builds a query behind the scenes).
Use ">=" and "<=" instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Gina Whipp]

Found the problem

Crit = "([szMileageFrom] <= " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] >= " & Mileage & ")"

I had >= and <= instead of <= and >=. Works fine now. Thanks ALL!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

What are you getting instead of 35.00? Does that value exist in the table?

Incidentally, your function is declared as being Currency, but DLookup
will return a Null value if you pass it a value without a corresponding
row in the table, which will cause an error.

To avoid the error, you should use

ServiceZone = Nz(DLookup("szChargeAmount", "tblServiceZones", Crit), 0.0)

to provide a default value.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dirk,

Thanks!!! That got rid of the error message but it's still not showing
me 35.00. What I am trying to do is take the distance between a and b
and if 0 to 50 it's 35.00, if 51 to 999 then it's 75.00. Am I going
about this the correct way or am I missing something?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

In Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in
VBA, but not in queries (and DLookup builds a query behind the scenes).
Use ">=" and "<=" instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[George Nicholson]

If you are getting zero, then I'd guess that Dlookup isn't finding a record.
I'd also guess that maybe szMileageFrom & To are text fields? That would
explain not finding any records.
If that's the case, try:

Crit = "(Cint([szMileageFrom]) => " & cint(Mileage) & ") "
Crit = Crit & "And (Cint([szMileageTo]) =< " & cint(Mileage) & ") "

I've also coerced Mileage because it is defaulting to a Variant data type
and we don't know what datatype is being passed into it.

*Don't* add single quotes to Crit in order to make Mileage a string value.
You want to be sure you are doing < and > comparisons on actual numbers.
Comparisons between numbers-as-text will give you unintended results. For
example, in the Immediate window:
?cstr(2)<=cstr(10)
will return False, just like a strict alpha sort would place both 1 and 10
before 2.


HTH,

Douglas,

Oops, forgot to say... I am getting a 0.

My table is: szMileageFrom szMileageTo szServiceCharge
0 50 35.00
51 999 75.00

So what I want is if the mileage falls between 0 and 50 to see 35.00 and
51 to 999 to see 75.00. By the way I did add the Nz function because I
realized if my mileage is null it does create an error.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

What are you getting instead of 35.00? Does that value exist in the
table?

Incidentally, your function is declared as being Currency, but DLookup
will return a Null value if you pass it a value without a corresponding
row in the table, which will cause an error.

To avoid the error, you should use

ServiceZone = Nz(DLookup("szChargeAmount", "tblServiceZones", Crit), 0.0)

to provide a default value.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dirk,

Thanks!!! That got rid of the error message but it's still not showing
me 35.00. What I am trying to do is take the distance between a and b
and if 0 to 50 it's 35.00, if 51 to 999 then it's 75.00. Am I going
about this the correct way or am I missing something?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
In Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in
VBA, but not in queries (and DLookup builds a query behind the scenes).
Use ">=" and "<=" instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Gina Whipp]

I found the problem:

Found the problem

Crit = "([szMileageFrom] <= " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] >= " & Mileage & ")"

I had >= and <= instead of <= and >=

FYI... szMileage fields are numeric.

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

If you are getting zero, then I'd guess that Dlookup isn't finding a
record.
I'd also guess that maybe szMileageFrom & To are text fields? That would
explain not finding any records.
If that's the case, try:

Crit = "(Cint([szMileageFrom]) => " & cint(Mileage) & ") "
Crit = Crit & "And (Cint([szMileageTo]) =< " & cint(Mileage) & ") "

I've also coerced Mileage because it is defaulting to a Variant data type
and we don't know what datatype is being passed into it.

*Don't* add single quotes to Crit in order to make Mileage a string value.
You want to be sure you are doing < and > comparisons on actual numbers.
Comparisons between numbers-as-text will give you unintended results. For
example, in the Immediate window:
?cstr(2)<=cstr(10)
will return False, just like a strict alpha sort would place both 1 and 10
before 2.


HTH,

Douglas,

Oops, forgot to say... I am getting a 0.

My table is: szMileageFrom szMileageTo szServiceCharge
0 50 35.00
51 999 75.00

So what I want is if the mileage falls between 0 and 50 to see 35.00 and
51 to 999 to see 75.00. By the way I did add the Nz function because I
realized if my mileage is null it does create an error.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

What are you getting instead of 35.00? Does that value exist in the
table?

Incidentally, your function is declared as being Currency, but DLookup
will return a Null value if you pass it a value without a corresponding
row in the table, which will cause an error.

To avoid the error, you should use

ServiceZone = Nz(DLookup("szChargeAmount", "tblServiceZones", Crit),
0.0)

to provide a default value.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dirk,

Thanks!!! That got rid of the error message but it's still not showing
me 35.00. What I am trying to do is take the distance between a and b
and if 0 to 50 it's 35.00, if 51 to 999 then it's 75.00. Am I going
about this the correct way or am I missing something?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
In Hello All,

I am at a loss why I can't get a simple price.

Public Function ServiceZone(Mileage) As Currency
'created 8.22.2007
Dim Crit

Crit = "([szMileageFrom] => " & Mileage & ") "
Crit = Crit & "And ([szMileageTo] =< " & Mileage & ") "

ServiceZone = DLookup("szChargeAmount", "tblServiceZones", Crit)
End Function

To Test:
?ServiceZone(4.5) <--- this should give me 35.00 but instead I get
a
syntax error "missing operator in query expression
'([szMileageFrom]=> 4.5 And ([szMileageTo] = < 4.5) ' ", highlighting
the DLookup line. Any help would be appreciated.

Jet SQL doesn't recognize the operators "=>" and "=<". They work in
VBA, but not in queries (and DLookup builds a query behind the
scenes). Use ">=" and "<=" instead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)