Insert and Update

[DonMoody]

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

 

[DonMoody]

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)

 

[DonMoody]

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)

 

[Nikos Yannacopoulos]

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

 

[DonMoody]

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))

 

[Nikos Yannacopoulos]

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!

 

[DonMoody]

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.

 

[Nikos Yannacopoulos]

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.


:

 

[DonMoody]

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

 

[DonMoody]

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 Yannacopoulos]

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