Query length issue (1 Viewer)

MW4

New member
Local time
Today, 08:00
Joined
Nov 1, 2017
Messages
8
I have data such as:

PO Num Qty
12345 10
12346 20
12345-1 5
12346-1 5
123 7
123-1 3

I want to create a summary count for the base left digits before the -x

PO Num Qty
12345 15
12346 25
123 10

I have tried many combos of queries and they all come out with a data type mismatch:

Any idea of how to do this....?
 

plog

Banishment Pending
Local time
Today, 10:00
Joined
May 11, 2011
Messages
11,613
First the admonishments:

1. If a field has "parts" to it, then you need to store that data in more fields. Each discrete piece of information needs to be stored discretely. Since you want to work with just the first part of the [PO Num] field, that should be in its own field. Would make this super simple.

2. Avoid spaces and special characters in names. Just makes coding and querying that much more difficult. [PO Num] -> [PONum]

Now, to solve your issue you would use 2 different access functions:

Mid(https://www.techonthenet.com/access/functions/string/mid.php) - which retrieves a substring from a string. You will use this to get just the characters you want from [PO Num]

Instr(https://www.techonthenet.com/access/functions/string/instr.php) - which finds the position of substrings/characters in strings. This will tell you what position the "-" is at in your [PO Num] field.

Together you can get just the first part of the value you need. Of course, the long term solution is to fix your tables as suggest above.
 

MW4

New member
Local time
Today, 08:00
Joined
Nov 1, 2017
Messages
8
Thanks, I understand points 1 and 2 very well, but unfortunately, it's not my data.

I had been using Mid using iif (Mid([po no], len(([po no])-2),1)="-"), left([po no]-2),[po no])

but like I said data mismatch...will instr fix that mismatch?
 
Last edited:

plog

Banishment Pending
Local time
Today, 10:00
Joined
May 11, 2011
Messages
11,613
It might--I can't really make sense of that code, it can't be syntatically correct. You would first test using Instr to see if a dash is in there, if so get the characters up to the dash, if not use the whole value.
 

MW4

New member
Local time
Today, 08:00
Joined
Nov 1, 2017
Messages
8
because the field length is different, but ones with dashes have a dash: Mid length - 2.
I queried for that and return the base left, then do a summary in query 2
 

MW4

New member
Local time
Today, 08:00
Joined
Nov 1, 2017
Messages
8
I tried this to test...what am I missing:

iif (instr([po no],"-",1) = >0), "yes", No")
 

plog

Banishment Pending
Local time
Today, 10:00
Joined
May 11, 2011
Messages
11,613
Not missing anything, have too much:
-extra argument in instr call--only need to pass it 2
-need to remove the =, you only care if instr returns a postive value
 

MW4

New member
Local time
Today, 08:00
Joined
Nov 1, 2017
Messages
8
iif (instr([po no],"-")>0 , "yes", No")

Expression has an invalid string
 

plog

Banishment Pending
Local time
Today, 10:00
Joined
May 11, 2011
Messages
11,613
You were missing something--a double quote mark before No
 

MW4

New member
Local time
Today, 08:00
Joined
Nov 1, 2017
Messages
8
This did it:

IIf(InStr([po no],"-")>0,Left([po no],InStr([po no],"-")-1),[po no])


Thanks for the help!!!!!!
 

Users who are viewing this thread

Top Bottom