Possible Multi-language problem (1 Viewer)

Birrel88

Registered User.
Local time
Today, 07:21
Joined
Sep 22, 2017
Messages
21
Possible Multi-language problem [Solved]

Hello,

I have encountered a problem with my access program, which I think could be a language problem.

I am creating a program on my computer. This computer is set to an english language (both windows and office/access).
The program itself is for a client which is using the Dutch language (windows and access)

Till now there weren't any problems. I updated the frontend a couple of times without any issues.

But today I made an update again, with a lot (more then 1000 lines) of VBA code.
When testing the application on my local pc (english) everythinb was working fine.
But after updating and testing it on the clients computer (dutch) the program had issues.

I am not posting any code right away, because it is a lot!
But roughly the program will check a date in a texybox, and looks into a table to see if a specific record exists, do a lot of checks and coding, and then changes a message and saves a new record if ot didnt exist yet.

I was thinking it could be a problem with the local datr formatting, so I decided to change the interface language of access on the clients computer to english.
That didn't work.

Is there anyone who knows that problems may occur when using a different language.
In my vba code, i checked if every date was surrounded by ##, it is.

If you need the vba code, I could post it, not a problem.
 
Last edited:

Birrel88

Registered User.
Local time
Today, 07:21
Joined
Sep 22, 2017
Messages
21
O, im sorry.
The issue is that the check, of a record already exists, doesnt seem to work on the dutch version.
It assumes a record doesnt exists, while it does
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:21
Joined
Aug 30, 2003
Messages
36,118
See if that link helps. In VBA, dates must be in US format; Allen provides a helpful solution.
 

Mark_

Longboard on the internet
Local time
Today, 07:21
Joined
Sep 12, 2017
Messages
2,111
I would double check the data and make sure you are comparing the same characters. If it is only a date you are interested in, make sure you are comparing the same exact data. Remember for date/time fields, they CAN hold a time even if they are formatted to only show the date portion.

Hopefully this is a quick check to make sure you DO have a problem first and not just bad data. If you can verify the data is good that should also point to where you real issue is.
 

Birrel88

Registered User.
Local time
Today, 07:21
Joined
Sep 22, 2017
Messages
21
Thank you for the replys.
I asked before I triple checked because maybe there's a simple thing you have to do with this case.
Tomorrow I am definitly going to triple check my code with th3 handout you guys have linked

I am not using a particular date in my code, only the input from. A textbox the user selects, and then check it with a date in the table. Going to check if both are just date and not time
 

Birrel88

Registered User.
Local time
Today, 07:21
Joined
Sep 22, 2017
Messages
21
So, I did some research on the code.
And this is what I came up with.

First of all, I have a Union Query. This query gets data from a couple of tables which hold information about customers, combined with the date they showed up and the expenses they have made.
This is working absolutely fine.

quniInkomstenLosseInvoer:
Code:
SELECT "consumpties" AS soort, tblInkomsten.inkomstenPK AS id1, "" AS id2, tblInkomsten.werknemerFK, tblInkomsten.soortActiviteit, tblInkomsten.commActiviteitFK, tblInkomsten.fld200GroepFK, tblInkomsten.datum, tblInkomsten.ingevoerdOp, [aantal] & "x Consumpties" AS omschrijving, tblInkomsten.aantalCons AS aantal, "" AS bedrag, tblFactuurNummers.gefactureerd, tblFactuurNummers.factuurNummer, tblFactuurNummers.betaald
FROM tblInkomsten LEFT JOIN tblFactuurNummers ON tblInkomsten.inkomstenPK = tblFactuurNummers.inkomstenFK
WHERE (((tblInkomsten.aantalCons) Is Not Null And (tblInkomsten.aantalCons)>0))
ORDER BY tblInkomsten.datum DESC;
UNION
SELECT "huur" AS soort, tblInkomsten_Huur.inkomstenHuurPK AS id1, "" AS id2, tblInkomsten.werknemerFK, tblInkomsten.soortActiviteit, tblInkomsten.commActiviteitFK, tblInkomsten.fld200GroepFK, tblInkomsten.datum, tblInkomsten.ingevoerdOp, [aantal] & "x Zaalhuur (" & [zaal] & ")" AS omschrijving, tblInkomsten_Huur.aantal, tblInkomsten_Huur.totaalHuur AS bedrag, tblFactuurNummers.gefactureerd, tblFactuurNummers.factuurNummer, tblFactuurNummers.betaald
FROM tblFactuurNummers RIGHT JOIN (tblInkomsten INNER JOIN tblInkomsten_Huur ON tblInkomsten.inkomstenPK = tblInkomsten_Huur.inkomstenFK) ON tblFactuurNummers.inkomstenHuurFK = tblInkomsten_Huur.inkomstenHuurPK
ORDER BY tblInkomsten.datum DESC;
UNION
SELECT "huurContant" AS soort, tblInkomsten.inkomstenPK AS id1, "" AS id2, tblInkomsten.werknemerFK, tblInkomsten.soortActiviteit, tblInkomsten.commActiviteitFK, tblInkomsten.fld200GroepFK, tblInkomsten.datum, tblInkomsten.ingevoerdOp, "Huur contant betaald" AS omschrijving, "" AS aantal, tblInkomsten.huurContantBedrag AS bedrag, "N.v.t." AS gefactureerd, "N.v.t." AS factuurNummer, "N.v.t." AS betaald
FROM tblFactuurNummers RIGHT JOIN tblInkomsten ON tblFactuurNummers.inkomstenFK = tblInkomsten.inkomstenPK
WHERE (((tblInkomsten.huurContantBedrag) Is Not Null And (tblInkomsten.huurContantBedrag)>0))
ORDER BY tblInkomsten.datum DESC;
UNION
SELECT "kassa" AS soort, tblInkomsten.inkomstenPK AS id1, "" AS id2, tblInkomsten.werknemerFK, tblInkomsten.soortActiviteit, tblInkomsten.commActiviteitFK, tblInkomsten.fld200GroepFK, tblInkomsten.datum, tblInkomsten.ingevoerdOp, "Inkomsten kassa" AS omschrijving, "" AS aantal, tblInkomsten.inkomstenKassa AS bedrag, "N.v.t." AS gefactureerd, "N.v.t." AS factuurNummer, "N.v.t." AS betaald
FROM tblFactuurNummers RIGHT JOIN tblInkomsten ON tblFactuurNummers.inkomstenFK = tblInkomsten.inkomstenPK
WHERE (((tblInkomsten.inkomstenKassa) Is Not Null And (tblInkomsten.inkomstenKassa)>0))
ORDER BY tblInkomsten.datum DESC;
UNION
SELECT "KT" AS soort, tblInkomsten.inkomstenPK AS id1, "" AS id2, tblInkomsten.werknemerFK, tblInkomsten.soortActiviteit, tblInkomsten.commActiviteitFK, tblInkomsten.fld200GroepFK, tblInkomsten.datum, tblInkomsten.ingevoerdOp, [aantalKT] & "x Koffie/Thee" AS omschrijving, tblInkomsten.aantalKT AS aantal, "" AS bedrag, tblFactuurNummers.gefactureerd, tblFactuurNummers.factuurNummer, tblFactuurNummers.betaald
FROM tblFactuurNummers RIGHT JOIN tblInkomsten ON tblFactuurNummers.inkomstenFK = tblInkomsten.inkomstenPK
WHERE (((tblInkomsten.aantalKT) Is Not Null And (tblInkomsten.aantalKT)>0))
ORDER BY tblInkomsten.datum DESC;
UNION SELECT "prijslijst" AS soort, tblInkomsten_Prijslijst.inkomstenFK AS id1, tblInkomsten_Prijslijst.artikelFK AS id2, tblInkomsten.werknemerFK, tblInkomsten.soortActiviteit, tblInkomsten.commActiviteitFK, tblInkomsten.fld200GroepFK, tblInkomsten.datum, tblInkomsten.ingevoerdOp, [aantal] & "x " & DLookUp("artikel","tblPrijslijst","artikelPK = " & [tblInkomsten_Prijslijst].[artikelFK] & "") AS omschrijving, tblInkomsten_Prijslijst.aantal, tblInkomsten_Prijslijst.PrijsTotBijAankoop AS bedrag, tblFactuurNummers.gefactureerd, tblFactuurNummers.factuurNummer, tblFactuurNummers.betaald
FROM (tblInkomsten INNER JOIN tblInkomsten_Prijslijst ON tblInkomsten.inkomstenPK = tblInkomsten_Prijslijst.inkomstenFK) LEFT JOIN tblFactuurNummers ON (tblInkomsten_Prijslijst.artikelFK = tblFactuurNummers.artikelFK) AND (tblInkomsten_Prijslijst.inkomstenFK = tblFactuurNummers.inkomstenFK)
ORDER BY tblInkomsten.datum DESC;

Now, I have a form. Which is used to fill in information about the expenses a customer have made. User has to select a date, then the type of customer, and then the customer from a combobox.
THen, the form shows a list of the current expenses already filled in. This list is the above union query. Working fine also.

The problem is the date.
The textbox where users has to fill in a date, has the following options:

Name: txtDatum
Control source: datum (It's bounded to a column in the table tblInkomsten (which is then also used in the above union query)
Format: Short date

When the date is filled in, and the customer selected in a combobox, the code will run.
The following line will run also:

Code:
Dim intAlIngevoerdeHuurContantRijen As Integer
            
intAlIngevoerdeHuurContantRijen = DCount("[soort]", "quniInkomstenLosseInvoer", "[soort] = 'huurContant' AND [datum] = #" & Me.txtDatum.Value & "# AND [commActiviteitFK] = " & Me.txtCommActiviteitFK.Value)

Now, when I run this on my computer. Which language is set to American English, everything works fine. And the variable intAlIngevoerdeHuurContantRijen returns an integer. The integer is the amount of rows in the union query.
If a row exists, it gives me a '1'. If not, a '0', if 2, then '2'.. And so on.

When I run this on the customers computer, which is set to the Dutch Language. It always returns zero rows. It can't seem to find a row with the filled in date.

What I tried, was setting the date language on the customers computer to american english. And voila, everything worked fine.
Except, this is not the solution I am looking for.

It seems my program can't 'translate' the dutch date format (8-9-2018 for today), to the date format I have selected my computer to (08-Sep-18).
I read the replied link, but just can't seem to find what I am doing wrong.
I used the ## around the date.

Can someone help me with this?
 

sonic8

AWF VIP
Local time
Today, 15:21
Joined
Oct 27, 2015
Messages
998
It seems my program can't 'translate' the dutch date format (8-9-2018 for today), to the date format I have selected my computer to (08-Sep-18).
That assessment is close to the truth.
The database engine processing the query does not understand the Dutch date format. So, you need to explicitly convert the date to something the db-engine does understand, which is either US date format (mm/dd/yyyy) or ISO date format (yyyy-mm-dd).



... AND [datum] = #" & Format(Me.txtDatum.Value, "yyyy-mm-dd") & "# AND ...


I published an extensive article on DateTime in Access+VBA on my website, including a paragraph dedicated to international scenarios. - I highly recommend that to you if you are developing an application in an mixed country/language environment.
 

Birrel88

Registered User.
Local time
Today, 07:21
Joined
Sep 22, 2017
Messages
21
Thank you for the reply.
The solution worked perfectly.

The article you provided, it is very helpfull and especially very easy and clear to read. Definitly word a bookmark.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:21
Joined
May 7, 2009
Messages
19,175
pbaldy has given you the answer but youre not listening. you are already creating a program for a client yet you dont know the basics. trial and error. call a friend.
 

Birrel88

Registered User.
Local time
Today, 07:21
Joined
Sep 22, 2017
Messages
21
pbaldy has given you the answer but youre not listening. you are already creating a program for a client yet you dont know the basics. trial and error. call a friend.


Have you considered the possibility, that the first link wasn't clear enough? I did read, but couldn't find the answer. The second link is a bit clearer for me, and after knowing the answer now, I see it is in the first link.
And so you tell me, because I don't know one thing (dates), I don't know all the basics?
Thought this forum was for people with problems who seek help, thanks.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:21
Joined
Aug 30, 2003
Messages
36,118
Thought this forum was for people with problems who seek help, thanks.

It is, please feel free to post again if/when you get stuck on something.
 

Users who are viewing this thread

Top Bottom