Help needed with IF DSum in query

[Guest]

Hi

I have created a transactions table in an inventory application. I have also
created a DSum function that shows the quantity in stock on a form, by
calculating the number of incoming transactions minus the number of outgoing
transactions.

However, I would like to include this DSum function on a query. The query
asks the user for what make of shoe, name, colour and size they want. I would
like the DSum function to just show the quantity in stock in a particular
size (the one asked for). At the moment it calculates all incoming and
outgoing transactions in the transaction table.

Is this the right syntax to do the above:-
=IF ("[Size]"=Like[Enter Size] & "*",
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE")

I hope I explained it okay, if there are any queries, please enail me.
Thanks for all your help, Louisa

 

[Guest]

Not sure but try it this way ---
=IF ([Size] Like[Enter Size] & "*",
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE")

Remove the qoutes from "[Size]" and remove the equal sign.

 

[Guest]

Hi
I wrote the following:-
=IF ([Size] Like[Enter Size] & "*"),
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE"))
But it came up with the follwing error:-
The expression you entered contains invald syntax, or you need to enclose
your text data in quotes.
Why do you think this error happens?
Thanks your being a great help

Not sure but try it this way ---
=IF ([Size] Like[Enter Size] & "*",
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE")

Remove the qoutes from "[Size]" and remove the equal sign.
--
KARL DEWEY
Build a little - Test a little

Hi

I have created a transactions table in an inventory application. I have also
created a DSum function that shows the quantity in stock on a form, by
calculating the number of incoming transactions minus the number of outgoing
transactions.

However, I would like to include this DSum function on a query. The query
asks the user for what make of shoe, name, colour and size they want. I would
like the DSum function to just show the quantity in stock in a particular
size (the one asked for). At the moment it calculates all incoming and
outgoing transactions in the transaction table.

Is this the right syntax to do the above:-
=IF ("[Size]"=Like[Enter Size] & "*",
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE")

I hope I explained it okay, if there are any queries, please enail me.
Thanks for all your help, Louisa

 

[John W. Vinson]

Hi
I wrote the following:-
=IF ([Size] Like[Enter Size] & "*"),
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE"))
But it came up with the follwing error:-
The expression you entered contains invald syntax, or you need to enclose
your text data in quotes.
Why do you think this error happens?
Thanks your being a great help

The function in Access is IIF(), not IF().

But... you have an expression which checks to see if the user's entery for
[Enter Size] matches the leftmost characters of the table field Size; if it
does, you sum ALL records in tblTransaction (regardless of size) for the two
fields and find the difference. I suspect that is NOT what you want to do!

Could you explain in words which records you want to sum? What's
tblTransactions related to - do you want the all Incoming transactions for a
given value of Size minus the sum of all outgoing transactions for that Size?
Why are you using LIKE: what are the values in Size? As written, if you have
sizes XS, S, M, L, XL, XXL and the user types X, you'll be summing all three X
sizes.

John W. Vinson [MVP]

 

[Guest]

Thanks for the reply.

I have created a stock table, which contains details of new shoes that have
been ordered and are kept at the warehouse before being sent top shops.

The user enters details into the transactions table through the use of a
form, when a shoe has been sold or sent to another shop.

I do you want add all Incoming transactions for a particular size minus the
sum of all outgoing transactions for that size.

I am using LIKE in my query, the query runs when a user wants to search
through all stock for a particular shoe. They are asked to enter the make,
number colour and size. I used LIKE so the user doesn't have to spell the
make etc exactly.

The values in size are 3, 4, 5, 6 etc - english sizes or 36, 37, 38, 39 etc
for continental sizes.

I hope I have explained this enough,
Thanks Louisa

Hi
I wrote the following:-
=IF ([Size] Like[Enter Size] & "*"),
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE"))
But it came up with the follwing error:-
The expression you entered contains invald syntax, or you need to enclose
your text data in quotes.
Why do you think this error happens?
Thanks your being a great help

The function in Access is IIF(), not IF().

But... you have an expression which checks to see if the user's entery for
[Enter Size] matches the leftmost characters of the table field Size; if it
does, you sum ALL records in tblTransaction (regardless of size) for the two
fields and find the difference. I suspect that is NOT what you want to do!

Could you explain in words which records you want to sum? What's
tblTransactions related to - do you want the all Incoming transactions for a
given value of Size minus the sum of all outgoing transactions for that Size?
Why are you using LIKE: what are the values in Size? As written, if you have
sizes XS, S, M, L, XL, XXL and the user types X, you'll be summing all three X
sizes.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks for the reply.

I have created a stock table, which contains details of new shoes that have
been ordered and are kept at the warehouse before being sent top shops.

The user enters details into the transactions table through the use of a
form, when a shoe has been sold or sent to another shop.

I do you want add all Incoming transactions for a particular size minus the
sum of all outgoing transactions for that size.

I am using LIKE in my query, the query runs when a user wants to search
through all stock for a particular shoe. They are asked to enter the make,
number colour and size. I used LIKE so the user doesn't have to spell the
make etc exactly.

The values in size are 3, 4, 5, 6 etc - english sizes or 36, 37, 38, 39 etc
for continental sizes.

It sounds like you still have a couple of design problems. You want to check
the inventory for all shoes of a certain make, colour and size: you CANNOT do
so by just checking the size. Even worse, you're allowing the user to type the
number 3 in a textbox and (I presume) assuming that this search will find only
Size 3 shoes. That assumption is incorrect! It will find all shoes where the
Size field begins with 3 - that is, all size 3, 36, 37, 38, 39 shoes.

I would strongly suggest a different approach. Perhaps you could have a Form
for entering orders, with three unbound combo boxes for make, number colour
and size. On this Form you could have a Subform based on your inventory table,
using these three combo boxes as the Master Link Field and the inventory
table's make, colour and size fields as Child Link Field: e.g.

[Make];[ColourCode];[Size]

This subform could be based directly on the inventory table so you could see
individual shoes, or on a Totals query summing the inventory amounts.

John W. Vinson [MVP]

 

[Guest]

The user searches for a shoe depending on make, number, colour and size.

How would I fix the problem of the user having to type the size in a textbox
and it finding multiple results?

I have tried your new suggestion, but I still need to search for a
particular shoe.

Thanks Louisa

 

[Guest]

Please can you tell me if the following syntax is correct:-
It should calculate the incoming minus the outgoing transactions depending
on the make, number, colour and size of shoe that the user enters.

IIF=([Make] Like[Enter Make] & "*", [Number] Like[Enter Number] & "*",
[Colour] Like[Enter Colour] & "*", [Size] Like[Enter Size] & "*"),
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE"))

 

[John W. Vinson]

Please can you tell me if the following syntax is correct:-
It should calculate the incoming minus the outgoing transactions depending
on the make, number, colour and size of shoe that the user enters.

IIF=([Make] Like[Enter Make] & "*", [Number] Like[Enter Number] & "*",
[Colour] Like[Enter Colour] & "*", [Size] Like[Enter Size] & "*"),
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE"))

I'm puzzled.

WHY do you insist on using wildcards?

You are making your job MUCH MORE DIFFICULT.

By using wildcards you will make it *impossible* for the user to find a size 3
shoe, because the search will find size 35, 36, 37 and so on.

I would - very strongly! - suggest that you *not* use prompts for the
parameters of your query. Instead, use a Form with Combo Boxes to allow the
user to *select* from a list, rather than having to type in, the value.

The syntax is incorrect; IIF is a function name and should be followed by a
parenthesis. For that matter the IIF isn't appropriate for this purpose
anyway; you instead want a query to retrieve records that match.

I think you may be down the wrong track in any case. If you're storing make,
size and colour in the transactions table, you may want to redesign your
tables. Could we back up a bit and describe the structure of your tables?
Might it not be simpler to have a single unique ShoeID field for each shoe (or
specific type of shoe, if the shoes table contains a quantity field)?

John W. Vinson [MVP]

 

[Guest]

Hi

I am trying to create an inventory application in Access 2007.

I have created the following tables:-
- Stock table (where information is entered and stored on each shoe
when it comes in)
- Transactions table (where information is stored on each transaction)

I enter shoe information into the stock table (through a form) when we have
recieved an order.

When we have sold a shoe, I would enter the details (through a form) into
the transactions table.

When I come to search for a shoe, to see which shop it is located in. I have
created a query, which runs when the button is pressed on the menu of the
inventory application. It asks for the make, number, colour and size needed,
I then want it to run and show the quantity in that specific size of shoe and
which shop it is located in. This is why I need an IIF DSum function in the
Quantity column, I thought this would calculate the incoming transactions
minus the outgoing transactions depending on that particular shoe.

I need your precious advice on how to create the IIF Dsum function?

I hope my explanation is clear to you.
Please do not hesitate to ask me any questions if you need to clarify before
being able to answer my question.

Thanks for all your help,
Louisa Holt.