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

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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: 390

Minty

AWF VIP
Local time
Today, 23:16
Joined
Jul 26, 2013
Messages
10,355
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

Well-known member
Local time
Today, 19:16
Joined
Apr 9, 2015
Messages
4,339
try converting the field in the append qry:
(strings or integers, etc)

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

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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.
 

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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.
 

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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, 23:16
Joined
Jul 26, 2013
Messages
10,355
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, 23:16
Joined
Jul 26, 2013
Messages
10,355
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.!
 

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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.
 

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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, 19:16
Joined
Feb 19, 2002
Messages
42,977
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.
 

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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.
Thanks @Pat Hartman for suggestion. I agree with you about the date field but my user want like this separately for months and years.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:16
Joined
Feb 19, 2002
Messages
42,977
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.
 

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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, 19:16
Joined
Feb 19, 2002
Messages
42,977
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
 

Pac-Man

Active member
Local time
Tomorrow, 04:16
Joined
Apr 14, 2020
Messages
408
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