Query field Times Form (1 Viewer)

[Infinite]

SELECT qryItemsSold.Show, tblItems.Model, IIf(([Date]>=[WStartDate]) And ([Date]<=[WEndDate]),Format(([GrossSales]*[WCost]),"Currency"),Format(([GrossSales]*[Forms]![frmWholeSale]![Text2]),"Currency")) AS Gross, qryItemsSold.Quantity, qryItemsSold.Date
FROM (qryItemsSold LEFT JOIN tblItems ON qryItemsSold.ModelID = tblItems.ModelID) LEFT JOIN tblItemWholeSale ON tblItems.Model = tblItemWholeSale.WItem
GROUP BY qryItemsSold.Show, tblItems.Model, IIf(([Date]>=[WStartDate]) And ([Date]<=[WEndDate]),Format(([GrossSales]*[WCost]),"Currency"),Format(([GrossSales]*[Forms]![frmWholeSale]![Text2]),"Currency")), qryItemsSold.Quantity, qryItemsSold.Date;

Hello! I am trying to get that to work, but:

Gross: IIf(([Date]>=[WStartDate]) And ([Date]<=[WEndDate]),Format(([GrossSales]*[WCost]),"Currency"),Format(([GrossSales]*[Forms]![frmWholeSale]![Text2]),"Currency"))

That doesnt work. Because of:

Format(([GrossSales]*[Forms]![frmWholeSale]![Text2])

That part. I am trying to times that value by that value from a field.

But, when I go to run the query, it asks me for a value.

Enter Parameter Value
Forms!frmWholeSale!Text2

So, what am I doing wrong? Thanks!

 

[arnelgp]

IIf(Date>=[WStartDate] And Date<=[WEndDate],Format([GrossSales]*[WCost],"Currency"),Format([GrossSales]*NZ([Forms]![frmWholeSale]![Text2], 0),"Currency"))

 

[Infinite]

That is still not working

Im not sure why though...I have 1 form, and in that form, is the text box, and that text box gets its records from one table. So, it should be doing everything fine. Im just not sure why its not...

 

[Infinite]

Any luck? I'm trying, but it still wont do it. Even when I try to reference to the table its getting its numbers from.

 

[CJ_London]

suggest take the format function out - you don't need it in the query anyway - just format the form or report where it is displayed.

 

[Infinite]

Expr1: IIf([Date]>=[WStartDate] And [Date]<=[WEndDate],Format([GrossSales]*[WCost],"Currency"),[GrossSales]*Nz([tblWholesale]![WholeSale Percent]))

That also doesnt work.

Expr1: IIf([Date]>=[WStartDate] And [Date]<=[WEndDate],Format([GrossSales]*[WCost],"Currency"),[GrossSales]*[tblWholesale]![WholeSale Percent])

Or that.

Expr1: IIf([Date]>=[WStartDate] And [Date]<=[WEndDate],Format([GrossSales]*[WCost],"Currency"),[GrossSales]*[frmWholeSale]![Text2])

Or that.

Its not the format, or the Nz. I am not sure what it is! I even referenced to the table like I said. No Joy.

 

[CJ_London]

also try simplifying your code

[GrossSales]*IIf(Date() between [WStartDate] And [WEndDate],[WCost],NZ([Forms]![frmWholeSale]![Text2], 0))

 

[CJ_London]

since you haven't explained what you mean by 'it doesn't work', can't help any more

 

[CJ_London]

you now have date in square brackets - Date is a reserved word - if you are using it in the context of its reserved use (i.e. todays date) then it should sjust be date or date(). If it is the name of a field, change the name of the field to something more meaningful

 

[Infinite]

since you haven't explained what you mean by 'it doesn't work', can't help any more

When I go to run the query it asks for a parameter value. That is not what I wanted.

 

[CJ_London]

@Infinite - you have some 350 posts on this forum, by now you should understand what sort of information responders need to be able to help.

If you can't be bothered to spend a few extra minutes providing that detail, why should responders bother to spend time asking for it again and again?

you have responded 'it asks for a parameter value'. So my answer is the equally vague 'you are missing a parameter.

I regret I can no longer help you. Good luck with your project

 

[Infinite]

The code that @arnelgp posted has not helped. I still have the same problems I described in the very first post. I am pretty sure I had the first post down pretty good. I explained what was wrong, the field, and what it says. And what I would need to fix it.

 

[CJ_London]

I see the original error in your first post, but since then there have been numerous 'tried this.. doesn't work, tried that....doesn't work'

if the error is as per your first post then either your form frmWholeSale doesn't exist or is not open or if open the control called Text2 doesn't exist.

Have you tried using the expression builder

 

[Infinite]

Have you tried using the expression builder

Yes, I tried using the expression builder. I tried to get the number from the main table, and the form. Using the expression builder. I'm really confused why it isnt working...I tried Text, Number (all field sizes and formats for number) and Currency.

When I tried each of those, the query (or any query that I create trying to point to the field/form) asks for a Parameter value. In the attached image, you can see what im talking about. That is just for the table. I can show you a image of it asking for it from the form, but im pretty sure if the table doesnt work, then neither will the form. Scratch that, I Know that it doesnt work.

Yes, I am sure that it has a number on it. I will paste the query here once more just to make sure I dont have something totally screwed up.

SELECT [B][COLOR="Red"]qryItemsSold.Show, tblItems.Model, IIf([Date]>=[WStartDate] And [Date]<=[WEndDate],Format([GrossSales]*[WCost],"Currency"),Format([GrossSales]*0.5,"Currency")) AS Expr1,[/COLOR] [/B][COLOR="Blue"]IIf([Date]>=[WStartDate] And [Date]<=[WEndDate],[GrossSales]*[WCost],[GrossSales]*[tblWholesale]![WholeSale Percent]) AS [Work][/COLOR]
FROM (tblItems LEFT JOIN tblItemWholeSale ON tblItems.Model = tblItemWholeSale.WItem) INNER JOIN qryItemsSold ON tblItems.ModelID = qryItemsSold.ModelID
GROUP BY qryItemsSold.Show, tblItems.Model, IIf([Date]>=[WStartDate] And [Date]<=[WEndDate],Format([GrossSales]*[WCost],"Currency"),Format([GrossSales]*0.5,"Currency")), IIf([Date]>=[WStartDate] And [Date]<=[WEndDate],[GrossSales]*[WCost],[GrossSales]*[tblWholesale]![WholeSale Percent]);

The code in red is what I am using for now since the code in blue isnt working.

 

[CJ_London]

probably because your query is looking for a table called tblWholeSale - but according to the query it should be tblitemwholesale

 

[Infinite]

tblitemwholesale is per item wholesale

tblWholeSale is the wholesale if the date of the item sold is not in between the tblItemWholesale Start and End date.

 

[CJ_London]

the point is - tblwholesale is not in your query

 

[Infinite]

I see your post, and I raise you one

Okay, I didn't know that the table had to be IN the query to use it.

My problem is, I want to be able to change a number, and I want that query to get its multiplication from that number. But, I want to not have to change that number in the query.

Is there a way to make a update query? So, I just write down the number, click, and the query changes to that number?

I also cant include the table in anything, as it only has 2 fields. WholeSaleID and Whole Sale Percent. Nothing to tie it to any of the other query's.