UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Ms Access 2010 Problem With VBA Code To Insert Dates, Access 2010    
 
   
Alhakeem1977
post Dec 19 2017, 11:31 AM
Post#1



Posts: 78
Joined: 8-July 17



How can I sort this issue to insert the format of a range of dates to the below code from a form called: frmInsertDates with two text boxes ( txtStartDate ) and ( txtEndDate ), the code is behind a button in the same form on Click event.

I tried the db in two versions MS Access 2013 and MS Access 2010 also with many date formats, it works fine in MS Access 2013 but not working in the other one.

CODE
Option Compare Database


Private Sub btnInsertSerial_Click()
On Error Resume Next

   Dim SratDate As Date
   Dim EndDate As Date
   Dim Seq As Integer
  
   Dim mydb As Database, MySet As Recordset
   Set mydb = CurrentDb()
  
   SratDate = Format$(txtStartDate, "yyyy/mm/dd")
   EndDate = Format$(txtEndDate, "yyyy/mm/dd")
    
    Seq = Nz(DMax("ID", "DeptSeq"), 0)
  
   Do While SratDate <= EndDate
  
  
          
          If Weekday(SratDate) <> 6 And Weekday(SratDate) <> 7 Then   ' not weekend
                If Nz(DLookup("HolidayID", "Holiday", " HolidayDate = #" & SratDate & "#"), 0) = 0 Then
                
                    Set MySet = mydb.OpenRecordset("Department", dbOpenDynaset, dbSeeChanges)
                    MySet.MoveFirst

                    Do While Not MySet.EOF
                    
                        Seq = Seq + 1
                        DoCmd.SetWarnings False
                        
                        DoCmd.RunSQL "INSERT INTO DeptSeq ( ID, DepartmentID, DDate )" & _
                                    " SELECT " & Seq & " ,  " & MySet!DepartmentID & " , #" & SratDate & "# "
                        
                        DoCmd.SetWarnings True
                        
                        MySet.MoveNext
                    Loop
                                  
                
                End If
            
          End If
    
    SratDate = DateAdd("d", 1, SratDate)
  
Loop

MySet.Close
mydb.Close
                    


End Sub


Thanks in advance.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
RuralGuy
post Dec 19 2017, 11:51 AM
Post#2


UtterAccess VIP
Posts: 2,823
Joined: 25-June 05
From: @ 8300' in the Colorado Rocky Mountains


Are you aware of the fact that Date data types are a special numeric type similar to the Currency data type? The output of the Format() is always a string. I believe you should separate the storing and manipulation of a date and the Display of same.

--------------------
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please reply to the forum so all may benefit.
Go to the top of the page
 
Doug Steele
post Dec 19 2017, 11:52 AM
Post#3


UtterAccess VIP
Posts: 21,666
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Realistically, dates in Access do not have a format: they're 8 byte floating point numbers where the integer portion represents the number of days since 30 Dec, 1899, and the decimal portion represents the time as a fraction of a day. That means that your attempts to format the dates before you assign them to the variable SratDate and EndDate don't do anything.

Try changing

CODE
   Dim SratDate As Date
   Dim EndDate As Date

to

CODE
   Dim SratDate As String
   Dim EndDate As String

(Note that this advice applies to all versions, not just Access 2010!)

--------------------
Go to the top of the page
 
Alhakeem1977
post Dec 19 2017, 12:26 PM
Post#4



Posts: 78
Joined: 8-July 17



Thanks all for your prompt reply, I tried to change it to As String instead of As Date but I got wrong range dates not as I selected it from the form then I change it again to As Date it works fine but again in MS Access 2013 but not in MS Access 2010.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Dec 19 2017, 05:01 PM
Post#5


UtterAccess VIP
Posts: 21,666
Joined: 8-January 07
From: St. Catharines, ON (Canada)


When you say you "got wrong range dates", what do you mean?

I'd missed the line of code If Weekday(SratDate) <> 6 And Weekday(SratDate) <> 7 Then. Changing SratDate to text conceivably could cause that line of code to fail. See whether If Weekday(Me!txtStartDate) <> 6 And Weekday(Me!txtStartDate) <> 7 Then works any better.

--------------------
Go to the top of the page
 
Alhakeem1977
post Dec 20 2017, 05:40 AM
Post#6



Posts: 78
Joined: 8-July 17



Thanks for your reply,
QUOTE
When you say you "got wrong range dates", what do you mean?

I meant when I run the code I'm not getting the expected range of calendar dates excluding the Fridays, Saturdays and Holidays or let say only getting the working days.

Actully, the problem only in the below:
CODE
SratDate = Format$(txtStartDate, "yyyy/mm/dd")
           EndDate = Format$(txtEndDate, "yyyy/mm/dd")


means when I enter the range of days from the form in the text boxes : txtStartDate and txtEndDate , it works fine in Ms Access 2013 but not wording in Ms Access 2013.

Thanks a lot

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Dec 20 2017, 07:26 AM
Post#7


UtterAccess VIP
Posts: 21,666
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What format is used for entering the dates in txtStartDate and txtEndDate? What is the Date format set to on the machine(s) where the code isn't working?

If you're using non-American date formats, Allen Browne's International Dates in Access might be useful for you.

--------------------
Go to the top of the page
 
HiTechCoach
post Dec 20 2017, 08:46 PM
Post#8


UtterAccess VIP
Posts: 18,996
Joined: 29-September 03
From: Oklahoma City, Oklahoma


I think the problem may be with your SQL INSERT statement.


The SELECT part is not complete. It needs a FROM table name.


It looks like you are wanting oo INSERT VAULES not data FROM a TABLE.

Try changing:

CODE
DoCmd.RunSQL "INSERT INTO DeptSeq ( ID, DepartmentID, DDate )" & _
                                    " SELECT " & Seq & " ,  " & MySet!DepartmentID & " , #" & SratDate & "# "


to this:

CODE
DoCmd.RunSQL "INSERT INTO DeptSeq ( ID, DepartmentID, DDate )" & _
                                    " Values (  " & Seq & " ,  " & MySet!DepartmentID & " , #" & SratDate & "#); " ' <<<<<




--------------------
Boyd Trimmell aka Hi Tech Coach ( HiTechCoach.com free Access stuff)
Microsoft MVP 2010-2015 - Access Expert
Inventory Control, Accounting, BPM, and CRM Software Developer
"If technology doesn't work for people, then it doesn't work."
Go to the top of the page
 
Doug Steele
post Dec 20 2017, 10:27 PM
Post#9


UtterAccess VIP
Posts: 21,666
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Boyd: You've probably hit the nail on the head.

AFAIR, there was a change in Access that didn't require the table name.

In other words,

CODE
INSERT INTO Table1 (Field1, Field2)
SELECT Value1, Value2

became legal.

Since it's working for Al in 2013, but not in 2010, that would seem to pinpoint when the change occurred.

--------------------
Go to the top of the page
 
Alhakeem1977
post Dec 21 2017, 07:32 AM
Post#10



Posts: 78
Joined: 8-July 17



Boyd: I tried it not working too.
compute.gif
This post has been edited by Alhakeem1977: Dec 21 2017, 07:34 AM

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Alhakeem1977
post Dec 21 2017, 09:46 AM
Post#11



Posts: 78
Joined: 8-July 17



Please help my db attached.

Appreciate your kind efforts.
Attached File(s)
Attached File  21_DEC_2017.zip ( 46.33K )Number of downloads: 7
 

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
moke123
post Dec 21 2017, 10:38 AM
Post#12



Posts: 1,237
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



First thing is you should add Option Explicit to the header of your code modules. you can set it to do it by default in your vbe settings.

CODE
If Weekday(SratDate) <> 6 And Weekday(SratDate) <> 7 Then   ' not weekend


Are weekends supposed to be Friday and Saturday? Your using day 6 (Friday) and day 7 (Saturday). Do you mean Saturday (7) and Sunday (1)?

edit: BTW, i'm using A2010 and it works fine as far as i can tell.
This post has been edited by moke123: Dec 21 2017, 10:50 AM
Go to the top of the page
 
Alhakeem1977
post Dec 21 2017, 11:14 AM
Post#13



Posts: 78
Joined: 8-July 17



QUOTE
Are weekends supposed to be Friday and Saturday? Your using day 6 (Friday) and day 7 (Saturday). Do you mean Saturday (7) and Sunday (1)?

Yes, in our country the weekends are Friday and Saturday and I want to exclude the holidays too.
My database attached.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
moke123
post Dec 21 2017, 12:03 PM
Post#14



Posts: 1,237
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



I'm using 2010 and it appears to be adding the right date range and is excluding the weekends and holidays.
where is it missing for you?
Go to the top of the page
 
Doug Steele
post Dec 21 2017, 12:51 PM
Post#15


UtterAccess VIP
Posts: 21,666
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You haven't answered the questions I asked. Since dates are dependent on the locale settings of the computer, your code may well work on everyone else's computer, but just not on yours...

--------------------
Go to the top of the page
 
Alhakeem1977
post Dec 21 2017, 11:35 PM
Post#16



Posts: 78
Joined: 8-July 17



QUOTE
What format is used for entering the dates in txtStartDate and txtEndDate? What is the Date format set to on the machine(s) where the code isn't working?

Sorry Mr. Doug for the delay, I really appreciate your prompt replies, in the text fields as Medium date, in the system is: dd-mmm-yy .
I checked the db in another computer with format mm-dd-yy , MS Access 2010 it's not working too.
But when I checked it another one with MS Access 2013 it works normally.

If it's possible for you to change the whole code which can do the same function I appreciate that, to get the Calendar working days applies for each three departments: Ops, GB-Cash and Zain excluding weekends Fridays and Saturdays and Holidays in our country is not fixed must be kept in a separate table.
Should be like this:
QUOTE
tblBatches

ID  Department  S.Date

1    Ops               01Jan17
2    Zain              01Jan17
3    GB                 01Jan17
4    Ops               02Jan17
5    Zain              02Jan17
6     GB               02Jan17


My db attached if it works in your pc may it will explain what I want.

Thanks in advance.


--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Dec 22 2017, 08:22 AM
Post#17


UtterAccess VIP
Posts: 21,666
Joined: 8-January 07
From: St. Catharines, ON (Canada)


It works fine on my machine too.

Try changing your code to

CODE
Private Sub btnInsertSerial_Click()
On Error GoTo ErrorHandler

Dim SratDate As Date
Dim EndDate As Date
Dim Seq As Long
Dim strSQL As String

Dim mydb As Database
Dim MySet As DAO.Recordset

  Set mydb = CurrentDb()
  
  Seq = Nz(DMax("ID", "DeptSeq"), 0)
  
  Do While SratDate <= EndDate
      
    If Weekday(SratDate) <> 6 And Weekday(SratDate) <> 7 Then   ' not weekend
      If Nz(DLookup("HolidayID", "Holiday", " HolidayDate = " & Format(SratDate, "\#yyyy\-mm\-dd\#")), 0) = 0 Then
                
        Set MySet = mydb.OpenRecordset("Department", dbOpenDynaset, dbSeeChanges)
        MySet.MoveFirst

        Do While Not MySet.EOF
                    
          Seq = Seq + 1
                      
          strSQL = "INSERT INTO DeptSeq ( ID, DepartmentID, DDate )" & _
            " SELECT " & Seq & " ,  " & MySet!DepartmentID & " , " & Format(SratDate, "\#yyyy\-mm\-dd\#")
          Debug.Print strSQL
          mydb.Execute strSQL, dbFailOnError
                      
          MySet.MoveNext
        Loop
      End If
    End If
    
    SratDate = DateAdd("d", 1, SratDate)
  Loop

Cleanup:
On Error Resume Next

  MySet.Close
  Set MySet = Nothing
  Set mydb = Nothing
  Exit Sub

ErrorHandler:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
  Resume Cleanup                

End Sub

After you run the code, if it's still not showing the correct results, go to the Debug window (Ctrl-G) and look at the SQL statements that are printed out there.

--------------------
Go to the top of the page
 
Alhakeem1977
post Dec 22 2017, 11:11 AM
Post#18



Posts: 78
Joined: 8-July 17



woohoo.gif Fantastic
Thank you very much, I will never forget your kind help.

You've missed the unbound two text boxes
CODE
SratDate = Format$(Me.txtStartDate, "yyyy/mm/dd")
  EndDate = Format$(Me.txtEndDate, "yyyy/mm/dd")
I added them and it works faster than the old code in MS Access 2013.
Thanks again appreciate your efforts.

* One more thing please how can make the ID starts with number 1 every year, I mean the DMax :
CODE
Seq = Nz(DMax("ID", "DeptSeq"), 0)

I tryed this code it works but starts to number 1 every time when I run the code :
CODE
Seq = Nz(DMax("[ID]", "DeptSeq", "Year([DDate]) = " & Year(Me.[DDate])), 0)

CODE
Private Sub btnInsertSerial_Click()
On Error GoTo ErrorHandler

Dim SratDate As Date
Dim EndDate As Date
Dim Seq As Long
Dim strSQL As String

Dim mydb As Database
Dim MySet As DAO.Recordset

  Set mydb = CurrentDb()
  '''''''''''''''''''''''''''''''''''''''''
  SratDate = Format$(Me.txtStartDate, "yyyy/mm/dd")
  EndDate = Format$(Me.txtEndDate, "yyyy/mm/dd")
  '''''''''''''''''''''''''''''''''''''''''
  Seq = Nz(DMax("ID", "DeptSeq"), 0)
  
  Do While SratDate <= EndDate
      
    If Weekday(SratDate) <> 6 And Weekday(SratDate) <> 7 Then   ' not weekend
       If Nz(DLookup("HolidayID", "Holiday", " HolidayDate = " & Format(SratDate, "\#yyyy\-mm\-dd\#")), 0) = 0 Then
                
        Set MySet = mydb.OpenRecordset("Department", dbOpenDynaset, dbSeeChanges)
        MySet.MoveFirst

        Do While Not MySet.EOF
                    
          Seq = Seq + 1
                      
          strSQL = "INSERT INTO DeptSeq ( ID, DepartmentID, DDate )" & _
            " SELECT " & Seq & " ,  " & MySet!DepartmentID & " , " & Format(SratDate, "\#yyyy\-mm\-dd\#")
          Debug.Print strSQL
          mydb.Execute strSQL, dbFailOnError
                      
          MySet.MoveNext
        Loop
      End If
    End If
    
    SratDate = DateAdd("d", 1, SratDate)
  Loop

Cleanup:
On Error Resume Next

  MySet.Close
  Set MySet = Nothing
  Set mydb = Nothing
  Exit Sub

ErrorHandler:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
  Resume Cleanup

End Sub


--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
Doug Steele
post Dec 22 2017, 01:41 PM
Post#19


UtterAccess VIP
Posts: 21,666
Joined: 8-January 07
From: St. Catharines, ON (Canada)


As I implied earlier, the two lines you added back in technically aren't correct. The Format function converts values to strings, so you're forcing data type coercion.

Replace them with

CODE
  SratDate = DateValue(Me.txtStartDate)
  EndDate = DateValue(Me.txtEndDate)

As to your ID issue, what is the point of this field? Technically, every table should have a primary key, and you haven't provided one for the DeptSeq table. If the value of ID is to reset (so that values get used more than once), that means it cannot serve as a primary key. And what do you want to have happen if txtStartDate is in one year, and txtEndDate is in the next year?

--------------------
Go to the top of the page
 
Alhakeem1977
post Dec 22 2017, 03:20 PM
Post#20



Posts: 78
Joined: 8-July 17



Thanks for your efforts.
QUOTE
As to your ID issue, what is the point of this field?

The ID is a batch ID of Daily Movement of transactions for three departments during a year and reset too, I placed an unbound text field to get this value under this format:( 001/17 ) for the first date and first Department and so on, unfortunately in our country the holidays are not fixed depends on the moon calendar.

I do have to exclude (delete) the holidays after the holiday date on a working day. and rearrange or add the the Calendar working days after the holiday.

QUOTE
every table should have a primary key


Shall I add a primary key in the table?

QUOTE
And what do you want to have happen if txtStartDate is in one year, and txtEndDate is in the next year?


I made anothe db for documents Log with same consept but without calendar days. It works perfect and you helped me in it earlier.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 06:24 AM