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
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