Solved Chinese Characters in Insert Query Datasheet View and Unable to to Insert Values (1 Viewer)

abdullahkhan

Member
Local time
Today, 18:14
Joined
Apr 14, 2020
Messages
268
Hi,

I'm using following query to Insert Values into tblSalary bit values are not being inserted and get error similar to unable to Insert values for to conversion failure.

SQL:
INSERT INTO tblSalary ( TeacherID, PayMonth, PayYear )
SELECT DISTINCT tblTeachers.TeacherID AS Expr1, Forms![frmSalary]!txtMonth AS expMonth, Forms![frmSalary]!txtYear AS expYear
FROM tblTeachers;

Datasheet View of the query show chinese (possibly) characters in month and year fields. I've tried everything I can think of but nothing worked. Db is attached. Can you please help me fix this issue?

Best Regards
Abdullah
 

Attachments

  • DoT.accdb
    1.4 MB · Views: 14

Minty

AWF VIP
Local time
Today, 13:14
Joined
Jul 26, 2013
Messages
8,023
Can I ask why you don't just store a payment date? It will make queries and everything else much easier in the long run.
You also have a lookup field in your table which isn't recommended.

You can always display the Month and Year from a paydate?

Finally the controls you are referring to as txtMonth has text values in it that are the bound value - that's why it won't work
 

Ranman256

Registered User.
Local time
Today, 08:14
Joined
Apr 9, 2015
Messages
3,766
try converting the field in the append qry:
(strings or integers, etc)

cStr([field]), cIng([field])
 

abdullahkhan

Member
Local time
Today, 18:14
Joined
Apr 14, 2020
Messages
268
Can I ask why you don't just store a payment date? It will make queries and everything else much easier in the long run.
Thanks for reply @Minty, purpose is to select any month and see whether any teacher had received the post of that month or not. Also, for entering teacher's pay, user select the month and year of which pay is being paid, he can entered pay if so the teachers. Secondly, this db is for my friend who is running a non profit institute and many time pay off a specific month is not given that month and it's paid next month and in that that pay date have different month.

the controls you are referring to as txtMonth has text values in it
txtMonth contain two columns, col1 is 1-12 for each month and is bound column. It does values as number. Column width is set do that column 1 is hidden and respective month is being shown instead of number.
 

abdullahkhan

Member
Local time
Today, 18:14
Joined
Apr 14, 2020
Messages
268
try converting the field in the append qry:
(strings or integers, etc)

cStr([field]), cIng([field])
Thanks @Ranman256, using CLng function works and issue is resolved. But I am unable to understand the reason because exactly the same methodology works in other form of the same database and I actually copied table, form, subform and qry from previously made objects and they started giving me this problem.
 

abdullahkhan

Member
Local time
Today, 18:14
Joined
Apr 14, 2020
Messages
268
You also have a lookup field in your table which isn't recommended.
@Minty are you referring to teacher id in tblSalary? I have used such setup in many other tables. If you're referring to this field then I should remove it and also from other tables as well. Any specific reason or issue which could be caused by this? I used it to get name of teacher instead of a number of I see the tables.
 

Minty

AWF VIP
Local time
Today, 13:14
Joined
Jul 26, 2013
Messages
8,023
Okay - sorry it must be a regional setting thing (reading right to left) as when I look at it it looks like this;

1608734762742.png


But if I cut and paste it the values look like this;
1;"جنوری";2;"فروری";3;"مارچ";4;"اپریل";5;"مئی";6;"جون";7;"جولائی";8;"اگست";9;"ستمبر";10;"اکتوبر";11;"نومبر";12;"دسمبر"
 

Minty

AWF VIP
Local time
Today, 13:14
Joined
Jul 26, 2013
Messages
8,023
The problem with lookup fields is that they hide the actual stored data. Have a read here http://access.mvps.org/Access/lookupfields.htm
They also can cause you speed issues, something I wouldn't have considered - I have just removed a load of them form a Client database I was working on.
One complex form that took 12 -15 seconds to load, is now only taking 3 seconds before I do anything else to speed things up.!
 

abdullahkhan

Member
Local time
Today, 18:14
Joined
Apr 14, 2020
Messages
268
The problem with lookup fields is that they hide the actual stored data. Have a read here http://access.mvps.org/Access/lookupfields.htm
They also can cause you speed issues, something I wouldn't have considered - I have just removed a load of them form a Client database I was working on.
One complex form that took 12 -15 seconds to load, is now only taking 3 seconds before I do anything else to speed things up.!
Thanks a lot for detailed explanation. Stay safe and healthy.
 

abdullahkhan

Member
Local time
Today, 18:14
Joined
Apr 14, 2020
Messages
268
Okay - sorry it must be a regional setting thing (reading right to left) as when I look at it it looks like this;

View attachment 87711

But if I cut and paste it the values look like this;
1;"جنوری";2;"فروری";3;"مارچ";4;"اپریل";5;"مئی";6;"جون";7;"جولائی";8;"اگست";9;"ستمبر";10;"اکتوبر";11;"نومبر";12;"دسمبر"
Yes, I'm using right to left orientation for this db.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:14
Joined
Feb 19, 2002
Messages
31,058
You will be happier in the long run if you use an actual date field rather than month and year. Just assume the first day of the month.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:14
Joined
Feb 19, 2002
Messages
31,058
Users should not be allowed to dictate how data is stored since data storage impacts the soundness of the application and the ease with which it can be expanded. Do what you want, but storing the data as a date and defaulting to day 1 is the best solution.
 

abdullahkhan

Member
Local time
Today, 18:14
Joined
Apr 14, 2020
Messages
268
Users should not be allowed to dictate how data is stored since data storage impacts the soundness of the application and the ease with which it can be expanded. Do what you want, but storing the data as a date and defaulting to day 1 is the best solution.
Thanks for reply. If I store as date then will I be able to set so that user have to select month and year only from two combo or one combo (for date) and one textbox for year. If yes then any suggestion how?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:14
Joined
Feb 19, 2002
Messages
31,058
There are complicated solutions and there are simple solutions. To enter a date and default to the current year, the user only has to enter the month/day. So 1/1 = 01/01/2020 (until next week), 12/1 = 12/01/2020, etc. This is probably going to be simpler than picking from two combos.

If you want to validate the date (instead of using the combos), put the code in the form's BeforeUpdate event. If you want to ensure, that only 1 is ever entered as day, do that in the control's AfterUpdate event. You can't update the control's value in the BeforeUpdate event.
Code:
If Day(Me.MyDate) = 1 Then
Else
    Me.MyDate = Year(Me.MyDate) & "/ &" Month(Me.MyDate) & "/1"
End If
 

abdullahkhan

Member
Local time
Today, 18:14
Joined
Apr 14, 2020
Messages
268
There are complicated solutions and there are simple solutions. To enter a date and default to the current year, the user only has to enter the month/day. So 1/1 = 01/01/2020 (until next week), 12/1 = 12/01/2020, etc. This is probably going to be simpler than picking from two combos.

If you want to validate the date (instead of using the combos), put the code in the form's BeforeUpdate event. If you want to ensure, that only 1 is ever entered as day, do that in the control's AfterUpdate event. You can't update the control's value in the BeforeUpdate event.
Code:
If Day(Me.MyDate) = 1 Then
Else
    Me.MyDate = Year(Me.MyDate) & "/ &" Month(Me.MyDate) & "/1"
End If
Thanks @Pat Hartman for the code. I'll try to implement it into my forms as I'm using month year approach in 4-5 forms.
 

Users who are viewing this thread

Top Bottom