Access HELP!!!!!!

Damski · 27 November 2006 16:06

OK, I know someone out there will have the answer to this for me, I’ve been fighting with it all day and gotten some of the way but now heed some help.

I’ve a single table, a two forms, on one form is a calender and a button, when the button is pressed it opens a second form with 5 data fields in it, one of them (locked) is the date from the previous pages calender. I enter the data into a blank record. easy…

now comes the crunch, I want to duplicate the data for the next 4 days. new records for each entry.

all the fields, apart from the date, are numbers, so no worries there.

Please help

this is what I have so far, it seems to work, except, when it reaches the month end it then starts adding to the month not the days,

please please help…


For i = 1 To 4

sSql = "INSERT INTO TBL_Data ([date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values (" & _
"#" & DateAdd("d", i, Format(Me.date, ddmmyyyy)) & "#, " & Me.Total_Manpower & ", " & Me.At_Work & ", " & Me.QRA & ", " & Me.On_Guard &  ") "
DoCmd.SetWarnings False
Debug.Print sSql

DoCmd.RunSQL sSql
DoCmd.SetWarnings True

Next i

STEP2000 · 28 November 2006 01:33

You need to test for the last day of Month.

Function :

Public Function DaysInMonth( _
intMonth As Integer, _
intYear As Integer) _
As Integer
’ Comments : Returns the number of days in a month
’ Parameters: intMonth - number of the month (1-12)
’ intYear - Year for the month for considering leap
’ years.
’ Note: Always specify the full four digits of the year
’ or the code cannot be considered Year 2000 compliant.
’ Returns : Number of days in the month
’ Source : Greg
’
Dim datDate As Date

On Error GoTo PROC_ERR

’ Last day of month
datDate = DateSerial(intYear, intMonth + 1, 0)

DaysInMonth = Day(datDate)

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
“DaysInMonth”
Resume PROC_EXIT

End Function

OR

ublic Function MonthFirstDay( _
intMonth As Integer, _
intYear As Integer) _
As Date
’ Comments : Returns the date of the first day of the month
’ Parameters: intMonth - Number of the month (1-12)
’ intYear - Year to check
’ Note: Always specify the full four digits of the year
’ or the code cannot be considered Year 2000 compliant.
’ Returns : Date of the first day of the month
’ Source : GREG
’
On Error GoTo PROC_ERR

MonthFirstDay = DateSerial(intYear, intMonth, 1)

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
“MonthFirstDay”
Resume PROC_EXIT

End Function

Example:

’ To use this example:
’ 1. Create a new form.
’ 2. Create a command button called cmdTest
’ 3. Paste the entire contents of this module into the
’ new form’s module.
’

Private Sub cmdTest_Click()
Dim datTest As Date
Dim datTest2 As Date
Dim intWeeks As Integer

datTest = CVDate(“12/13/1990”)
datTest2 = CVDate(“05/12/1998”)

Debug.Print datTest & " plus 30 weekdays is " & _
AddWeekdays(datTest, 30)

Debug.Print “If you were born on " & datTest & _
“, you would be " & AgeCalc(datTest, intWeeks) & _
" years (” & intWeeks & " weeks) old”

Debug.Print “There are " & DaysInMonth(2, 1999) & _
" days in February, 1999.”

Debug.Print "There are " & DiffDays(datTest, datTest2) & _
" days between " & datTest & " and " & datTest2

Debug.Print "There are " & DiffDaysFractional(datTest, datTest2) & _
" fractional days between " & datTest & " and " & datTest2

Debug.Print "There are " & DiffMinutes(datTest, datTest2) & _
" minutes between " & datTest & " and " & datTest2

Debug.Print "There are " & DiffMinutesFractional(datTest, datTest2) & _
" fractional minutes between " & datTest & " and " & datTest2

Debug.Print "There are " & DiffSeconds(datTest, datTest2) & _
" seconds between " & datTest & " and " & datTest2

Debug.Print "There are " & DiffWeekdays(datTest, datTest2) & _
" weekdays between " & datTest & " and " & datTest2

Debug.Print "There are " & DiffWeeks(datTest, datTest2) & _
" weeks between " & datTest & " and " & datTest2

Debug.Print "The first day of the week for date " & datTest & _
" is " & FirstDayOfWeek(datTest)

Debug.Print "The last day of the week for date " & datTest & _
" is " & LastDayOfWeek(datTest)

Debug.Print "The last Sunday in the month for date " & _
datTest & " is " & LastDayOfWeekInMonth(12, 1990, 1)

Debug.Print "The first day of the Feburary 1999 is " & _
MonthFirstDay(2, 1999)

Debug.Print "The first weekday in Feburary 1999 is " & _
MonthFirstWeekday(2, 1999)

Debug.Print "The last day of the month in February 2000 is " & _
MonthLastDay(2, 2000)

Debug.Print "The last weekday of the month in February 2000 is " & _
MonthLastWeekday(2, 2000)

Debug.Print "The day after 02/28/2000 is " & _
NextDate(#2/28/2000#)

Debug.Print "The next Sunday after " & datTest & " is " & _
NextDOW(datTest, 1)

Debug.Print "The next weekday after " & datTest & " is " & _
NextWeekday(datTest)

Debug.Print "The 2nd Sunday of February 1998 is " & _
NthDayOfMonth(2, 1998, 2, 1)
Debug.Print "One day before " & datTest & " is " & _
PriorDate(datTest)

Debug.Print "The last Sunday before " & datTest & " is " & _
PriorDOW(datTest, 1)

Debug.Print "The previous weekday before " & datTest & " is " & _
PriorWeekday(datTest)

Debug.Print "First day of the quarter for date " & datTest _
& " is " & QuarterFirstDay(datTest)

Debug.Print "The last day of the quarter for date " & datTest _
& " is " & QuarterLastDay(datTest)

Debug.Print “I’m waiting for 5 seconds…”
WaitSeconds 5
Debug.Print “… done.”

Debug.Print "There are " & YearsBetweenDates(datTest, datTest2) _
& " years between " & datTest & " and " & datTest2

Debug.Print "1990 " & _
IIf(IsLeapYear(1990), "is “, “isn’t”) & _
" a leap year.”

Debug.Print "1992 " & _
IIf(IsLeapYear(1992), "is “, “isn’t”) & _
" a leap year.”

Debug.Print “2000 " & _
IIf(IsLeapYear2(2000), “is”, “isn’t”) & _
" a leap year.”

End Sub

Damski · 28 November 2006 11:19

OK, so I think I may have gotten wires crossed here, the output from the imidete window is right, it’s when it hits the access table it screws up, the output is here, for a middle of the month date, you get this.

INSERT INTO TBL_Data ([area], [date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values ('3 Sqn A Shift', #20/11/2006#, 10, 10, 10, 10) 
INSERT INTO TBL_Data ([area], [date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values ('3 Sqn A Shift', #21/11/2006#, 10, 10, 10, 10) 
INSERT INTO TBL_Data ([area], [date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values ('3 Sqn A Shift', #22/11/2006#, 10, 10, 10, 10) 
INSERT INTO TBL_Data ([area], [date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values ('3 Sqn A Shift', #23/11/2006#, 10, 10, 10, 10)

and it works fine.

when it covers the end of a month you get this

INSERT INTO TBL_Data ([area], [date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values ('3 Sqn A Shift', #30/11/2006#, 10, 10, 10, 10) 
INSERT INTO TBL_Data ([area], [date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values ('3 Sqn A Shift', #01/12/2006#, 10, 10, 10, 10) 
INSERT INTO TBL_Data ([area], [date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values ('3 Sqn A Shift', #02/12/2006#, 10, 10, 10, 10) 
INSERT INTO TBL_Data ([area], [date], [Total_Manpower], [At_Work], [QRA], [On_Guard]) Values ('3 Sqn A Shift', #03/12/2006#, 10, 10, 10, 10)

it all looks to be correct, but when I look at the data in the table it’s put the data in, and it’s ok up untill the end of the month, it swapps the day and date about, so I get data saved for thr 30th november then twelth of jan, then twelth of feb and twelth of march.

HELP

any other workround would also be appreciated.

Kevin · 28 November 2006 12:52

With, for example, 30 November you can only write that one way round - 30 cannot be a month. When you hit 1 december either number could be interpreted as a month. I would check the date settings for Access, Windows and anything else that is involved to ensure that they all work dd/mm/yyyy.

STEP2000 · 28 November 2006 14:42

The Table format should handle your import if set to DATE format.
One other thing you could try is:

Dim Date1 as Date
Date1 = Format(Date,“MMDDYYYY”)

Date1 = Date1 + 1

Msgbox Date1

and see if that adds to your date in the proper method. If so then your system is setup correct to store the data. If not then you have to change the Access Date ormat and also make sure Windows date format is set.

you could also days in month function and use that for your calc on the insert

jontyc · 28 November 2006 21:23

ms access rule #34
always format dates (at least medium) when using in code

Damski · 28 November 2006 22:54

I had our access guru look at it at work, and his answer, dont do it… great help, messing with access and SQL commands mixed with dates is just a nightmare, we ended up forcing the SQL command to format the US way mmddyyyy and for some odd reason thats worked, oh well, it’s working now, just the reast of the database to build before Friday lunch!

Cheers for the pointers guys

Droid · 28 November 2006 22:58

Dates are a pain in other MS products. Create a database with dates in it in Excel and mailmerge it with a Word document and it will not allow you to have the date UK style. It always defaults to US. If I copy and paste the data into a Word table and merge that, it works fine Oh well!! :rolleyes: