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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Criteria "like" Problem, Access 2003    
 
   
Pencil
post Sep 3 2019, 04:29 PM
Post#1



Posts: 17
Joined: 5-November 11



I have a inventory parts list in query that I am trying to figure out how to set two fields to look up different rates for my customers on a form. I have the original price set for the part and I would like two different rates to charge to my customers. Rate 1 would increase the original price by 15% if they are a good paying customer within 30days. Then I have a Rate 2 which would increase the original price by 20% for customers taking longer then 30 days to pay.

How would I have the criteria set for this I thought I cold use something like..
Like "*" & [Rate # ????] & "*" This just does not seem to work and I am stumped

Any help would be much appreciated..
Go to the top of the page
 
theDBguy
post Sep 3 2019, 04:38 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


Hi. Sounds more like you'll need to use an IIf() statement here. Can you post your query SQL? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Pencil
post Sep 3 2019, 04:42 PM
Post#3



Posts: 17
Joined: 5-November 11



thanks theDBguy for the quick replay. I haven't used access for years and just starting to get back into it. Here is the SQL

SELECT DISTINCTROW [Work Order Parts].*, Tbl_Parts_List.Cost, Tbl_Parts_List.[Stock Code], Tbl_Parts_List.[English Description], [Work Order Parts].Quantity, [Cost]*[Quantity] AS [Total Cost], [Cost]*0.15 AS [15% Rating1], [Cost]*0.2 AS [20% Rating2], [15% Rating1]+[Cost] AS Rate1, [20% Rating2]+[Cost] AS Rate2
FROM [Work Order Parts] LEFT JOIN Tbl_Parts_List ON [Work Order Parts].[Parts List] = Tbl_Parts_List.[Stock Code]
ORDER BY Tbl_Parts_List.[Stock Code];
Go to the top of the page
 
theDBguy
post Sep 3 2019, 04:44 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks. Okay, I don't see anywhere in that query where you are checking if it's for a good or not so good customer. If so, maybe you're just trying to add the two price columns and simply use one or the other later on, based on the customer. Is that correct?


--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Sep 3 2019, 04:46 PM
Post#5


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


You could make a table for the rates and then put a combo box on the form.

--------------------
Robert Crouser
Go to the top of the page
 
Pencil
post Sep 3 2019, 05:09 PM
Post#6



Posts: 17
Joined: 5-November 11



thanks for all the help. It would be nice to set the good customer thing up with a date field when the have paid but it might get to complex for me to figure that out. I never thought about using a table with combo box and select the rates there, I am not sure this will work either as it would have to adjust all parts on the invoice for me. I will try that option, thanks for the suggest projecttoday..

My thought was if when opening the form it would pop-up asking rate 1 or rate 2 but again I am not great in this complex world of access, just know enough to be dangerous.

Ill try the combo box and see if this will work for me... Might be back with more questions

thanks again...
Go to the top of the page
 
projecttoday
post Sep 3 2019, 05:29 PM
Post#7


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


When I typed in my post, I had not seen posts 3 and 4.

What is the purpose of the form? If it's just for reporting then I don't think you want a combo box.

--------------------
Robert Crouser
Go to the top of the page
 
Pencil
post Sep 3 2019, 06:52 PM
Post#8



Posts: 17
Joined: 5-November 11



Yep I try the query to include the if statement but have a bit of a problem getting the sorted out. I created a table with Rating 1 & 2 and in the next column a put the .15 and .20 as percentage then went to the query to build the if statement but having problems. Wish I would have used access more over the years, as I have build some systems before it just take me time to figure it out again.

Maybe there is an easier way..
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 06:50 PM