Chinese characters in result set (1 Viewer)

[liddlem]

In my solution, we plan and manage travel arrangements for remote students at the beginning and end of each term. (To get them to the boarding school and back home again.)

To extract the list of potential students that will be traveling inward,
I have a form containing 3 fields - Tvl_Year, TVL_Term and QNo (aka 'batch number')
All three of these fields are correctly displaying as numeric values.

I then have the following query to select the appropriate records and INSERT them into the dbo_TVL_STU_TRAVEL table.

INSERT INTO dbo_TVL_STU_TRAVEL ( ID_Student, Tvl_Yr, Tvl_Term, REQ_NO, CRN, STUDENT_CONTACT_NO, TVL_FROM, TVL_TO, ETD, Route, TVL_Direction )
SELECT dbo_STU_Student.ID_Student,
Val([Forms]![NAV_CLO_TVL]![NavigationSubform].[Form]![TVL_Year]) AS Tvl_Yr,
Val([Forms]![NAV_CLO_TVL]![NavigationSubform].[Form]![TVL_Term]) AS Tvl_Term,
Val([Forms]![NAV_CLO_TVL]![NavigationSubform].[Form]![QNo]) AS RqNo,
dbo_STU_Student.PERS_CRN, dbo_STU_Student.PERS_Mobile, dbo_STU_Student.ID_Postcode,
3 AS TvlTo,
Date() AS ETD,
0 AS Route,
"IN" AS Tvl_Direction
FROM dbo_STU_Student
WHERE (((dbo_STU_Student.ID_Att_Type)<5) AND ((dbo_STU_Student.ID_ENR_Stage)<7));

So I have a few questions:
1. The query result displays Chinese characters for three fields that are referenced on the form ....UNLESS I use Val(RefToForm) to convert to a value.
Initially, I thought it may be because the form fields were not formatted to a 'General number' - fixed that...no change in result
Then I thought maybe it was because the form was not bound to an underlying table? I created a table and bound it to the form. Still no difference.

2. The problem only presents if the result field is a DERIVED value based on the form field (I.E. the resultant field is created on the fly)
If I create a simple query that filters data based on the form fields, the result returns the correct data. So I presume that access is somehow interpreting 'RAW' (character base value?) data on the form?
So why cant/doesn't it return the result in the correct font?

3. Sometimes....not always, I get an error saying that the query is too complex and it doesn't run.
I have an access 2016 front end and SQL 2016 back end.

4. I also noticed that if I manually type the field criteria (For example: Val([Forms]![NAV_CLO_TVL]![NavigationSubform].[Form]![TVL_Year]) AS Tvl_Yr) in the query designer, then when I run the query, I get asked to input the value in a pop-up bacause it seems to have lost its way to my form field.
However, If I use the BUILDER tool to generate exactly the same output, my query suddenly knows where to source the data.
Any idea what's going on here? (I am just curious to understand why this happens)
Thanks

 

[sneuberg]

1. The query result displays Chinese characters for three fields that are referenced on the form ....UNLESS I use Val(RefToForm) to convert to a value.

If you are talking about viewing the query in datasheet view I recall seeing that sort of problem but the form data was inserted correctly, not the screwy stuff I was seeing. Have you run the query to see what it is actually being inserted? In my case I found I had to use the Chr function if I want to see the data in the datasheet view but in the end since the insert was working I took them out.

3. Sometimes....not always, I get an error saying that the query is too complex and it doesn't run.

I hate that. What the f is "too complex" supposed to mean? Every time I got this message it turned out to a type mismatch rather than anything having to do with complexity. I suggest verifying that the type of the data you are trying to insert matches the types of the target table.

 

[liddlem]

Yeah - that's the strange thing.
When the query runs, it is inserting numeric values into the table - but if I simply run the query as a SELECT, then I see the 'garbage'.

As for M$ error message - I couldn't agree more. My problem MUST have something to do with the data type by virtue that it works when I use the VAL() function.
Maybe an incompatibility between SQL 2016 and Access? I know that Access upsizes a number to some obscure number format [I don't recall what type] in SQL.
The resultant numbers in the SQL table are all integers.

But there again, my SELECT query (which reads the number from a form field) and assigns that value as a common value for all records) is giving the problem.

But the SELECT query that uses the value in the form field as a CONDITION, doesn't seem to mind about the formatting or the Val() function - It JUST works?

The problem therefore seems to be with the derived field?
Or maybe (as I hinted in my original post) there is something else related to building a query by hand as opposed to using the builder tool? Does the builder establish some sort of secret handshake?

 

[The_Doc_Man]

The builder does not have a secret handshake, but you should be able to see exactly what it built and decide that for itself.