MS Access Union Query Changes Numeric values to Text values

[Anigito]

Dear Community,

My question might be a very simple to answer, since I haven't found related threads in the forum. I would really appreciate your support.

What I have:
2019 MS Office
Excel files with the same formatting used as linked tables

What I try to achieve:
Union necessary data for later processing via Access/Excel

What has been done:
Union query which looks like this:

SELECT 
KA, 
[MODEL#SUFFIX] AS MODEL, 
GDMI_Types.rTYPE as MEASURE, 
VALUE, 
YEAR as YYYY, 
MONTH as MM, 
WEEK as WW

FROM GDMI_2019_Q LEFT JOIN GDMI_Types ON GDMI_2019_Q.MEASURE = GDMI_Types.GDMI_TYPE

UNION ALL SELECT 
KA, 
[MODEL#SUFFIX] AS MODEL, 
GDMI_Types.rTYPE as MEASURE, 
VALUE, 
YEAR as YYYY, 
MONTH as MM, 
WEEK as WW

FROM GDMI_2020_Q LEFT JOIN GDMI_Types ON GDMI_2020_Q.MEASURE = GDMI_Types.GDMI_TYPE;

Error:
Formatting for VALUE, YYYY, MM, WW should be number (as it is in the source file).
The query has been working well until recently, what exactly caused the issue, I cannot seem to find.
But the result of the query turns these values into text, disrupting further processing and reporting.

Question:
How to fix this format change?
I have tried to use VAL() function for numeric values, but it did not work.

 

[Joe4]

So this data is coming from Excel?
If so, then Access tries to automatically figure out the data type from the first 10-20 rows of data.
If you have any non-numeric values in the first 10-20 rows for these fields (even "blanks"), that might cause Access to give it a text data type instead of number.

Also note: I would HIGHLY recommend changing the field names in your Excel files, if at all possible.
Using Reserved words (words of existing functions) like "Year" and "Month" could cause unexpected issues and behavior.
I would recommend using different field names, if at all possible.

 

[Anigito]

So this data is coming from Excel?
If so, then Access tries to automatically figure out the data type from the first 10-20 rows of data.
If you have any non-numeric values in the first 10-20 rows for these fields (even "blanks"), that might cause Access to give it a text data type instead of number.

Yes, data comes from Excel, and till the very recent, it was working fine. The dataset is exactly the same.

Using Reserved words (words of existing functions) like "Year" and "Month" could cause unexpected issues and behavior.

Same statement as before, the query was working fine (NB. I am working on VM, and I now incline to the possibility of some changes on server side, that cause this).
However, I shall try and change the field names just for the sake of it. Thank you.

 

[Joe4]

Yes, data comes from Excel, and till the very recent, it was working fine. The dataset is exactly the same.

No changes have been made to the data?
Note that Access may adjust it "real-time" according to the current data which is found in the file.
So if the data has changed, the data type may too.
I would check the data very closely.

 

[Anigito]

I would check the data very closely.

I shall do that again.

The data comes from the following processing (original data is matrix style, converting to table, please don't judge, I am not programmer), so, basically only "Week" might have an issue, since it is not set as value, but all the others are (NB. code has been successfully working for 5 years).

Sub TRANSFORM()

Dim AR() As Variant: AR = Range(Cells(1, 1), Cells(ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column)).Value
Dim HeadCount As Long: HeadCount = 1
Dim Cols As Long: Cols = UBound(AR, 2)
Dim Ros As Long: Ros = (UBound(AR) - HeadCount) * Cols
Dim res() As Variant: ReDim res(1 To Ros, 1 To Cols - HeadCount)
Dim pos As Long: pos = 1

Dim myRange As Object
Dim zeroRow As Object

Dim lRow As Long
Dim m As Long
Dim cell As Range

Dim wsn As String
Dim yrn As String
Dim destpath As String

Start:
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

destpath = ' here be the data path .. and dragons

For i = 1 + HeadCount To UBound(AR) Step 1
    For j = 3 To UBound(AR, 2) - HeadCount Step 1
            res(pos, 1) = AR(i, 1)
            res(pos, 2) = AR(i, 2)
            res(pos, 3) = AR(i, 3)
            res(pos, 4) = AR(i, j + 1)
            res(pos, 5) = Val(20 & Left(AR(1, j + 1), 2)) ' YEAR
            res(pos, 6) = Val(Mid(AR(1, j + 1), 4, 2)) + IIf( _
                Weekday(WorksheetFunction.EoMonth(DateSerial(Val(20 & Left(AR(1, j + 1), 2)), Val(Mid(AR(1, j + 1), 4, 2)), Val(Mid(AR(1, j + 1), 7, 2))), 0), vbMonday) < 4 _
                And Val(Mid(AR(1, j + 1), 4, 2)) <> DatePart("m", DateSerial(Val(20 & Left(AR(1, j + 1), 2)), Val(Mid(AR(1, j + 1), 4, 2)), 6 + Val(Mid(AR(1, j + 1), 7, 2)))), _
                1, 0) ' month
'            res(pos, 6) = Val(Mid(AR(1, j + 1), 4, 2)) ' month
            res(pos, 7) = Replace(Mid(AR(1, j + 1), 12, 2), ")", "", 1, 1, vbBinaryCompare) ' week
            res(pos, 8) = Val(res(pos, 5) & Format(res(pos, 7), "00"))
            If res(pos, 4) = 0 Then
                res(pos, 9) = 0
                Else
                res(pos, 9) = 1
            End If
            pos = pos + 1
    Next j
Next i

' setting variables for file and sheet naming

yrn = "20" & Left(Sheets(1).Range("E1"), 2)

If Right(Sheets(1).Range("C2"), 3) = "Amt" Then
wsn = "A"
Else
If Sheets(1).Range("A1") = "Subs" Then
wsn = "H"
Else
wsn = "Q"
End If
End If

Workbooks.Open (destpath & "GDMI_" & yrn & ".xlsm")

' Sheets.Add(After:=Sheets(Sheets.Count)).Name = wsn


With Sheets(wsn)
    .Cells.ClearContents
    .Range("A1:I1") = Array("KA", "MODEL.SUFFIX", "MEASURE", "VALUE", "YEAR", "MONTH", "WEEK", "INTERFACE-DATE", "CHECK")
    .Range("A2").Resize(UBound(res), UBound(res, 2)).Value = res

' == sorting and deleting unnecessary lines
    lRow = .Range("A" & Rows.Count).End(xlUp).Row
    Set myRange = .Range("A1:I" & lRow)
    myRange.Sort Key1:=.Range("I1"), Order1:=xlDescending, Header:=xlYes
    Set zeroRow = .Range("I:I").Find(What:="0", LookIn:=xlValues)

' deleting rows -- first sort and find the first 0 value in column, delete all below
    .Rows(zeroRow.Row & ":" & lRow).EntireRow.Delete
'    .Columns.AutoFit
End With

Workbooks("GDMI_" & yrn & ".xlsm").Close savechanges:=True
ActiveWorkbook.Close savechanges:=False

Set myRange = Nothing
Set zeroRow = Nothing

Finish:
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

End Sub

 

[Micron]

Updates to M$ applications seem to have effects such that things that used to work, don't anymore so "it ran for x years" really ensures nothing. However, these effects are usually experienced when the underlying cause is poor syntax, use of reserved words, malformed code, etc. I base this notion on having participated in a lot of cases where it used to work and one or more of those situations, when fixed, resolved the problem. In addition, over the recent past a few updates have introduced bugs that needed to be fixed in emergency releases so there's that too.

If you want to link sheets and use them as tables, you're better off doing that and replicating the sheets as properly designed tables. You then run update/append queries to copy over the sheet rows to the tables as records. Now the tables will not interpret data types but will use the proper types that you have designed in the table fields. You use these tables, not the linked sheets.

Another possibility is to try conversion functions on the incorrect types; e.g. CDbl(strYear) - note that I used strYear and not "Year". 158% agree - change the reserved words. You will never have this issue if you incorporate prefixes that pertain to data types, plus this always makes the type evident throughout your code. You never have to go back to the beginning to see what varMyVariable is because var denotes it as variant. For Access, do make use of this list:

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

www.allenbrowne.com

and adopt a naming convention
- General: Commonly used naming conventions
- MS Access Naming Conventions

 

[james_lankford]

import your Excel data into Access and stop using it as linked tables

 

[Anigito]

import your Excel data into Access and stop using it as linked tables

Not an option, sorry. Thank you for feedback, though.

No changes have been made to the data?
Note that Access may adjust it "real-time" according to the current data which is found in the file.
So if the data has changed, the data type may too.
I would check the data very closely.

I did re-check everything, starting from the linked table design and format. As I suspected, the solution was real simple.

Solution:
DO NOT union table without records in it.
If source table contains only headers, and you are using UNION query, the query will result in failure (see above).

I removed the union to an empty source table, and everything got back to normal.
Thank you for your feedback.

NB!

So this data is coming from Excel?
If so, then Access tries to automatically figure out the data type from the first 10-20 rows of data.

Checked this one on another source/report. This is also true.

 

[Joe4]

Thank you for reporting back what the issue was.
Didn't realize that you might have empty tables.

 

[Anigito]

Thank you for reporting back what the issue was.
Didn't realize that you might have empty tables.

Yeah, as it may be clear from the macro, it creates sheet, depending on the contents of the raw data. And, when one sheet already contains records, the others do not. So a little adjustment to the query solved the issue.