UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Open Report Where....    
 
   
dhill23
post Apr 3 2012, 01:24 PM
Post #1

New Member
Posts: 17
Trying to open a report (based off a query) where the following are true....

[CustomerType] = "COMM" and [ProductLine] is between "1400" and "1604" or
[CustomerType] = "COMM" and [ProductLine] is between "3000" and "8030"

This works for the first 2 conditions, but i can't figure out how to add the 3rd condition.

DoCmd.OpenReport stDocName, acPreview, , "CustomerType = 'COMM' AND [ProductLine] BETWEEN '1400' AND '1604'"

Any help woudl be appreciated!
Go to the top of the page
 
+
accesshawaii
post Apr 3 2012, 01:31 PM
Post #2

UtterAccess VIP
Posts: 4,589
From: From Hawaii - Now in Wisconsin...Am I Nuts?
I'm not sure how that is working at all, if you have quotes, it's reading it as a string variable. The fields should be numeric and be as.

[CustomerType] = "COMM" and [ProductLine] between 1400 and 1604
Go to the top of the page
 
+
arnelgp
post Apr 3 2012, 01:33 PM
Post #3

UtterAccess Ruler
Posts: 1,090
"[CustomerType] = 'COMM' and ( [ProductLine] is between '1400' and '1604' or " & _
"[ProductLine] is between '3000' and '8030' Or " & _
"[ProductLine] .. your condition )"
Go to the top of the page
 
+
dhill23
post Apr 3 2012, 01:54 PM
Post #4

New Member
Posts: 17
i'm getting a syntax error when i do as you suggested. Is this what you are suggesting?

DoCmd.OpenReport stDocName, acPreview, , "CustomerType = 'COMM' and ([ProductLine] is between '1400' and '1604' or [ProductLine] is between '3000' and '8030')"
Go to the top of the page
 
+
pere_de_chipstic...
post Apr 3 2012, 04:14 PM
Post #5

UtterAccess VIP
Posts: 7,584
From: South coast, England
If you are using 'BETWEEN' then the variables must be numeric, ie must not have quotation marks around the figures, your code should read e.g.

DoCmd.OpenReport stDocName, acPreview, , "CustomerType = 'COMM' AND (([ProductLine] is BETWEEN 1400 AND 1604) OR ([ProductLine] is BETWEEN 3000 AND 8030))"

hth
Go to the top of the page
 
+
dhill23
post Apr 3 2012, 08:00 PM
Post #6

New Member
Posts: 17
The ProductLine field is a text field unfortunately. This is an ODBC linked table so i don't have any control over that. Any other ideas considering that?
Go to the top of the page
 
+
arnelgp
post Apr 4 2012, 01:11 AM
Post #7

UtterAccess Ruler
Posts: 1,090
CODE
DoCmd.OpenReport stDocName, acPreview, , "CustomerType = 'COMM' and ([ProductLine] is between '1400' and '1604' or [ProductLine] is between '3000' and '8030')"


will this work:
CODE
DoCmd.OpenReport stDocName, acPreview, , "CustomerType = 'COMM' and (Val([ProductLine]) between 1400 and 1604 or Val([ProductLine]) between 3000 and 8030)"


actually your original code will work ONLY remove the "IS" in "is between", should be: [ProductLine] Between '1440' and '1604'

This post has been edited by arnelgp: Apr 4 2012, 01:15 AM
Go to the top of the page
 
+
pere_de_chipstic...
post Apr 4 2012, 04:22 AM
Post #8

UtterAccess VIP
Posts: 7,584
From: South coast, England
QUOTE
remove the "IS" in "is between


Good Catch arnelgp!

I should have spotted that when I copied the text across (oops! (IMG:style_emoticons/default/blush.gif) ) )
Go to the top of the page
 
+
arnelgp
post Apr 4 2012, 04:59 AM
Post #9

UtterAccess Ruler
Posts: 1,090
I hardly noticed it at first Bernie, in small letters.
Go to the top of the page
 
+
dhill23
post Apr 4 2012, 04:18 PM
Post #10

New Member
Posts: 17
taking out the "Is" did the trick. Thank you so much!!!!
Go to the top of the page
 
+
« Next Oldest · Access Reports · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 01:33 AM