Make-Table Action query column data (1 Viewer)

[Gunnerp245]

I use a Make-Table Action query to import data from a linked table into my database. The linked table is on a network server that is automatically updated.

One column of the linked table is named "QTY/PARTIAL" and approximately 10% of the 500 records have a "P" after a number i.e. 1000 P. I would like to separate the number and the P into separate columns in the new table to faciliate being able to compute the total number "QTY".

Thanks,
Gunner...

 

[FoFa]

Create a query that handles the split of the field, one column for the number portion and one for the P (or NULL?). Than run your make table against that query.
But if it is a linked table, why do you need to run a maketable query? Can't you just use it as a linked table?

 

[Gunnerp245]

FoFa,
That is where I am stuck. I am unable to configure the query to split the original field. I tried the MID([QTY/PARTIAL],1) but this leaves the "P" in the QTY field and puts whatever is in the last position in the PARTIAL field.

Your assistance in showing me how to split the field would be appreciated.

The reason for the make-table is the linked table is huge and it takes forever for my queries and reports to run. Also, I can work on the information at home, where linked I can not.

Gunner...

 

[neileg]

Make two calculated fields in your query.

QTY: val([QTY/Partial])
PARTIAL: iif(Right([QTY/PARTIAL],1)="P","P","")

 

[Gunnerp245]

nieleg,
The "PARTIAL: iif(Right([QTY/PARTIAL],1)="P","P","")" is working great!
However, when I add "QTY: val([QTY/Partial])" as a calculated field, I get a "Data type mismatch in criteria expression.". I looked up the VAL function, however the description was pretty vague in the book I have. But it appears that the field in the table needs to be number. Mine is "text".

Slight clarification of first post information. I import specific data via a macro using the 'transferspreadsheet' function from a huge spreadsheet using "import specification" vice a linked table. The make-table query is run against this first table to produce a second. If I change the spec for the QTY/Partial field from text to number will I loose the "P"?

I keep the database size in check by deleting the first table again via macro after creation of the second table.

Appreciate OUTSTANDING assistance!
Gunner...

 

[FoFa]

You can use the same code:
QTY: iif(Right([QTY/PARTIAL],1)="P",VAL(LEFT([QTY/PARTIAL],(LEN([QTY/PARTIAL])-1)),VAL([QTY/PARTIAL]))
SO basically it is checking for your P and bypassing the P part, or just using the whole thing.

 

[Gunnerp245]

This command,

QTY: iif(Right([QTY/PARTIAL],1)="P",VAL(LEFT([QTY/PARTIAL],(LEN([QTY/PARTIAL])-1)),VAL([QTY/PARTIAL]))


produces, "The expression you entered has a function containing the wrong number of arguements".

Gunner...

 

[FoFa]

QTY: iif(Right([QTY/PARTIAL],1)="P",VAL(LEFT([QTY/PARTIAL],LEN([QTY/PARTIAL])-1)),VAL([QTY/PARTIAL]))

Had an extra "(" in there

 

[Gunnerp245]

FoFa
I now get a "Data type mismatch in criteria expression". I mentioned in a earlier post that the field is "text", will that matter?

Gunner...

 

[FoFa]

Is there any other alpha data in field beside the "P"?

 

[Gunnerp245]

Here is a sample of data in the QTY/Partial column

Could it be the "space" between the number and the "P"?
Gunner...

 

[FoFa]

A space usually dosn't bother the VAL function. Is there always a space? If so we can modify this to account for it as such:
QTY: iif(Right(trim([QTY/PARTIAL]),1)="P",VAL(LEFT([QTY/PARTIAL],LEN([QTY/PARTIAL])-2)),VAL([QTY/PARTIAL]))

 

[Gunnerp245]

Success!

FoFa,

There is always a space when there is a "P" present. Not sure whether one is there when a "P" is not.
It gave the same error. I change the last parameter by removing the VAL function at it works; It now reads;

QTY: IIf(Right(Trim([QTY/PARTIAL]),1)="P",Val(Left([QTY/PARTIAL],Len([QTY/PARTIAL])-2)),([QTY/PARTIAL]))

Thanks A Million! I learned alot!
Gunner...