SQL insert into with Hijri date

[Busbait]

I have a problem with Insert into SQL statement. When I use the below SQL to insert a Hijri date , the date will be stored in the table as Gregorian date.

How can I specify the date that I am inserting into the table is Hijri date not Gregorian date.

dbsCurrent.Execute "INSERT INTO Rent ([RentDueDate]) VALUES" & "(#" & [Text_RentDueDate] & "#)"

[Scott Price]

Try:

Application.SetOption "Use Hijri Calendar"

Regards,
Scott

[Busbait]

Thanks Scott for your replay, but still its not working

I will explain the problem with more details and examples as follows:

When I use the below code to insert the Hijri date “2 / 9 / 1428” which is equivalent to “14 / 9 / 2007” , MS Access will recognize and insert the date “2 / 9 / 1428” as Gregorian date and since I have already selected the option to use “Hijri Calendar”, MS Access will convert “2 / 9 / 1428” as Gregorian to “14 / 04 /831” as Hijri date , which is not correct as the original Hijri date is “2 / 9 / 1428”

The final result that will be stored in the table is “14 / 04 /831” as Hijri date and as you can see it is totally different from the date that I have used in the code “2 / 9 / 1428”.

Your help is highly appreciated
----------------------------------------------------------------------------------------
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb
Application.SetOption "Use Hijri Calendar", True
Calendar = vbCalHijri

dbsCurrent.Execute "Insert Into Rent ([RentDueDate]) VALUES (#" 2 / 9 / 1428 "#)"
dbsCurrent.Close

[Scott Price]

Have you changed the regional settings in the Windows control panel to make sure they are using Hijri date also?

Regards,
Scott

[Busbait]

Yes, I have already changed the regional settings to use Hijri date, but no luck

[Scott Price]

Yes, I have already changed the regional settings to use Hijri date, but no luck

Well, after a little digging around I found this in the Access/VBA help file:

Use International Date Formats in SQL Statements
See AlsoSpecificsYou must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.

Try setting your query up in the query design grid, and then opening it from vb code.

Regards,
Scott

[Busbait]

Thanks Scott for your efforts

I will check the query design grid and see the results

[MAKADA]

I know this a pretty old thread, but here is the solution in case it benefits anyone:

Add
calendar=vbcalgreg
before
dbsCurrent.Execute "INSERT INTO Rent ([RentDueDate]) VALUES" & "(#" & [Text_RentDueDate] & "#)"
and then at the end
add
calendar=vbcalhijri

this way date is converted to gregorian and stored, so when it is retrieved it is then converted back into hijri

( It works in ms access 2007)

[uzairawan1]

SQL/MS Access dont support hijri calender you just add 1000 years into hijri years and save it to sql and while retrieving just minus 1000 years everything will be ok... ;) like if your date is 01/05/1429 this will be 01/05/2429 and will be save easily.. and while retrieve minus 1000years willl be on its original stat..

[NeoPa]

Access supports the Hijri calendar. This has no effect on how dates are stored though. Today doesn't stop being today because I think of it in Hijri or Gregorian. SQL doesn't support Hijri literal dates (which I think is what this is actually about). Using literal dates in SQL is the same for any type of calendar and is done using m/d/yyyy format (as has been mentioned already in this thread). See Literal DateTimes and Their Delimiters (#) for more on this.