Insert and Update

  • Thread starter Thread starter Guest
  • Start date Start date
Nikos,

The following is my code and the date format in my listbox is dd/mm/yyyy,
same with all other dates in my db. the code includes the original that you
sent me.

thanks again Nikos


Dim strSQL As String

Date1 = Me.lsbxAvailableEmployees.Column(1)
vYear = Val(Left(Date1, 2))
vMonth = Val(Mid(Date1, 4, 2))
vDay = Val(Right(Date1, 2))
Date2 = DateSerial(vYear, vMonth, vDay)

strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate,
Duration )"
strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders
strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0)
strSQL = strSQL & " , " & Date2
strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2)
DoCmd.RunSQL strSQL

Date1 = Me.lsbxAvailableEmployees.Column(1)
Date2 = DateSerial(Year(Date1), Month(Date1), Day(Date1))

strSQL = "DELETE * FROM tblEmployeeAvailability"
strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0)
strSQL = strSQL & " AND StartDate = #" & Date2 & "#"
DoCmd.RunSQL strSQL
 
Nikos,

I have also tried this, i changed the date parameters
is this correct, is it changed to dd/mm/yyyy

Date1 = Me.lsbxAvailableEmployees.Column(1)
vDay = Val(Left(Date1, 2))
vMonth = Val(Mid(Date1, 4, 2))
vYear = Val(Right(Date1, 4))
Date2 = DateSerial(vDay, vMonth, vYear)
 
Nikos,
sorry Nikos, i did the following and i am still having the same problem

I have also tried this, i changed the date parameters
is this correct, is it changed to dd/mm/yyyy

Date1 = Me.lsbxAvailableEmployees.Column(1)
vDay = Val(Left(Date1, 2))
vMonth = Val(Mid(Date1, 4, 2))
vYear = Val(Right(Date1, 4))
Date2 = DateSerial(vDay, vMonth, vYear)
 
Don,

If your date format is dd/mm/yyyy then 05/01/06 was a very bad example!
In light of this, change the date manipulation code to:

Date1 = Me.lsbxAvailableEmployees.Column(1)
vYear = Val(Right(Date1, 4))
vMonth = Val(Mid(Date1, 4, 2))
vDay = Val(Left(Date1, 2))
Date2 = DateSerial(vYear, vMonth, vDay)

Nikos
 
Really, Really Sorry Nikos, i just got mixed up becuase of how it was being
displayed on the allocation table, i have put the following code in, it is
still doing the same.

Date1 = Me.lsbxAvailableEmployees.Column(1)
vYear = Val(Right(Date1, 4))
vDay = Val(Left(Date1, 2))
vMonth = Val(Mid(Date1, 4, 2))
 
You are confusing me. Try to work out how Left, Right and Mid work, and
adjust your code to what the lsitbox returns (check out Access help on
the syntax, if in doubt).

Also check out th syntax for DateSerial, the arguments are ALWAYS year,
month, day, so don't change that!
 
Sorry Nikos,

i sent a couple of message at the same time.

i have added to following and the syntex is correct, year, month and day

Date1 = Me.lsbxAvailableEmployees.Column(1)
vYear = Val(Right(Date1, 4))
vMonth = Val(Mid(Date1, 4, 2))
vDay = Val(Left(Date1, 2))

this is still appending the allocation table with the incorrect date and not
dleting the availability table.
 
What is the value assigned to Date2?
Sorry Nikos,

i sent a couple of message at the same time.

i have added to following and the syntex is correct, year, month and day

Date1 = Me.lsbxAvailableEmployees.Column(1)
vYear = Val(Right(Date1, 4))
vMonth = Val(Mid(Date1, 4, 2))
vDay = Val(Left(Date1, 2))

this is still appending the allocation table with the incorrect date and not
dleting the availability table.


:
Click to expand...
 
Nikos, these are the following parameters that i have got :

thanks for the reply, sorry, i must be doing something stupid

Date1 = Me.lsbxAvailableEmployees.Column(1)
vYear = Val(Right(Date1, 4))
vMonth = Val(Mid(Date1, 4, 2))
vDay = Val(Left(Date1, 2))
Date2 = DateSerial(vYear, vMonth, vDay)

I have not changed the date2 expression
 
Nikos, these are the following parameters that i have got :

thanks for the reply, sorry, i must be doing something stupid

Date1 = Me.lsbxAvailableEmployees.Column(1)
vYear = Val(Right(Date1, 4))
vMonth = Val(Mid(Date1, 4, 2))
vDay = Val(Left(Date1, 2))
Date2 = DateSerial(vYear, vMonth, vDay)

I have not changed the date2 expression
 
Don,

This is getting us nowhere. If you want, compact and zip your database
and mail it to me so I can have a look. I don't see how I can help you
otherwise.

Nikos
 
Back
Top