Union Query to only select numbers

[Uvuriel03]

Hey, I tried looking for answers to this, but unfortunately the words I'm using in my search are too small or too common.

I've got this union query:

SELECT Max(tblAFIN.[TRUCK]) AS [NEXTTRUCK]
FROM tblAFIN;
UNION ALL SELECT Max(tblAFOUT.[TRUCK]) AS [NEXTTRUCK]
FROM tblAFOUT;

And I only want it to pull wholly-numeric values from the list.

For example, if I have these values in the [TRUCK] column:
1
2
3
AF070708
4
5

It would only pull the numbers to add to the union query results:
1
2
3
4
5

And not the AF070708 since this one contains letters.

I don't know how to do this though, or honestly if it's even possible--any ideas?

 

[pbaldy]

Try adding this to both sections:

WHERE IsNumeric([TRUCK])

 

[LPurvis]

Hi there.

It's a question of limiting the rows returned - and you do that in the Where clause.
It's then a question of choosing how we select numbers only.

SELECT [TRUCK] AS [NEXTTRUCK]
FROM tblAFIN WHERE Val([TRUCK]) = [TRUCK]
UNION ALL
SELECT [TRUCK]
FROM tblAFOUT WHERE Val([TRUCK]) = [TRUCK]

Which is susceptable to Null values (if you have any).
You could go with the much more obvious

SELECT [TRUCK] AS [NEXTTRUCK]
FROM tblAFIN WHERE IsNumeric([TRUCK])
UNION ALL
SELECT [TRUCK]
FROM tblAFOUT WHERE IsNumeric([TRUCK])

Regardless using function calls in your criteria will slow the query down.
You won't notice with only a few records. (few < thousands).

 

[LPurvis]

(I elaborated... it's my way... :-s)

 

[Uvuriel03]

Thank you so much! Worked like a charm!