run time error 13 data mismatch to do with dates

[asya]

Hello,

I'm trying to run the following code and get type mismatch 13 error

Basically, i'm getting the 3rd field name along in table "ImportedTable", put that value on Combo3 and try run a transpose on the table. The field names (dates) in the transposed table get generated based on the value of Combo3.

The error points to the line:
FieldName = Format(Me.Combo3 + (7 * (i - 1)), "dd-mmm-yyyy")

the fieldname is a string...

how do i do about fixing this one?

Private Sub cmdAppend_Click()
Dim Con As ADODB.Connection
Set Con = CurrentProject.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
sql = "select * from ImportedTable"" "
With rs
.ActiveConnection = Con
.Open (sql)
End With
 
Combo3.Value = rs.Fields(rs.Fields.Count - 74).Name
 
 End Sub
Private Sub Command5_Click()
 
 
  Dim db As DAO.Database
  Dim tblDef As DAO.TableDef
  Dim sql As String
  Dim FieldName As String
  Dim i As Integer
  Dim j As Integer
 
  Set db = CurrentDb
  Set tblDef = db.TableDefs("ImportedTable")
 
  ' delete any old records from NewTable.
  db.Execute "Delete * from [NewTable]"
 
  ' append records to NewTable from ImportedTable.
i = 1
 
  For i = i To 40
j = i + 2
FieldName = Format(Me.Combo3 + (7 * (i - 1)), "dd-mmm-yyyy")
 
 
    sql = "Insert Into [NewTable] " & _
          "Select [ImportedTable].[EmployeeID], [ImportedTable].[ProjectID],#" & _
          FieldName & "# as [Week], [" & _
          tblDef.Fields(j).Name & "] as [Workload] " & _
          "from [ImportedTable]"
     db.Execute sql
 
 
 Next i
  MsgBox "Records appended from ImportedTable to NewTable."
  Set tblDef = Nothing
  Set db = Nothing
  Exit Sub
 
ErrHandler:
  MsgBox Err.Description
 
End Sub

 

[Kiwiman]

Howzit

I knwo that SQL likes to deal in american date formats, so it could be becoming unstuck here. Try

FieldName = Format(Me.Combo3 + (7 * (i - 1)), "mm-dd-yyyy")

 

[asya]

Hi Kiwiman,

I've tried the suggested solution , still the same error unfortunately...

Howzit

I knwo that SQL likes to deal in american date formats, so it could be becoming unstuck here. Try

FieldName = Format(Me.Combo3 + (7 * (i - 1)), "mm-dd-yyyy")

 

[Kiwiman]

Howzit

What does the value of fieldname show after the first iteration:

j = i + 2
FieldName = Format(Me.Combo3 + (7 * (i - 1)), "dd-mmm-yyyy")
debug.print fieldname

THis will show in your immediate window of your code

 

[asya]

Hi Kiwiman,

I added it to the code and nothing appears in the immediate window...maybe I'm doing something wrong

Would combo3.value is boolean in the watch and fieldname is integer have something to do with the mismatch?...

Thanks heaps for your help,

Asya

Howzit

What does the value of fieldname show after the first iteration:

j = i + 2
FieldName = Format(Me.Combo3 + (7 * (i - 1)), "dd-mmm-yyyy")
debug.print fieldname

THis will show in your immediate window of your code

 

[asya]

Hi Kiwiman,

I've tried to amend the code as suggested, but nothing appears in the immediate window..maybe i'm doing it wrong...

Would the combo3.value being boolean and fieldname being integer have something to do with the mismatch?

Thank you for your help

Asya

Howzit

What does the value of fieldname show after the first iteration:

j = i + 2
FieldName = Format(Me.Combo3 + (7 * (i - 1)), "dd-mmm-yyyy")
debug.print fieldname

THis will show in your immediate window of your code

 

[Kiwiman]

Howzit

My apologies - of course nothing will show as the error occurs on this line before the debug.print. Try

j = i + 2
debug.print me.combo3
FieldName = Format(Me.Combo3 + (7 * (i - 1)), "dd-mmm-yyyy")

 

[RainLover]

Try

FieldName = Me.Combo3 + (7 * (i - 1))

Untested

 

[asya]

Hello,

it prints 06/11/11, so i tried FieldName = Format(Me.Combo3 + (7 * (i - 1)), "dd-mm-yy"), but still no success, tried without formating- no luck either.

Interestingy, if i do not run the two codes together, the fieldname doesn't get an error...they must clash somehow


Asya

Howzit

My apologies - of course nothing will show as the error occurs on this line before the debug.print. Try

j = i + 2
debug.print me.combo3
FieldName = Format(Me.Combo3 + (7 * (i - 1)), "dd-mmm-yyyy")

 

[vbaInet]

You said it prints 06/11/11, are you talking about Me.Combo3? And is that the last printed result before it errors?

If it is then I would be inclined to wrap that in CDate() before you Format(). Or perform the calculations in two steps.

 

[ChrisO]

Asya.

Please post the regional settings that your computer is using.
Please also post the Debug.Print of sql before it is executed.

Chris.

 

[ChrisO]

It looks like vbaInet is correct, you are adding numbers to a string.

MsgBox "06/11/11" + (7 * (i - 1))

 

[vbaInet]

It looks like vbaInet is correct, you are adding numbers to a string.

MsgBox "06/11/11" + (7 * (i - 1))

Precisely Chris.

CDate() or DateValue()

Format(CDate(Me.Combo3) + (7 * (i - 1)), "dd-mmm-yyyy")
Format(DateValue(Me.Combo3) + (7 * (i - 1)), "dd-mmm-yyyy")

OR

Just use DateAdd() and you won't need to cast it to date.

Format(DateAdd("d", 7 * (i - 1), Me.Combo3), "dd-mmm-yyyy")

 

[taloker]

I am facing a problem
2 tables LU Product and Sales Point
I want the price from LU product populates when I enter the product in Sales point form
The code I have written is as below
Private Sub Price_AfterUpdate() Product = DLookup("Product", "LU Product", "Price=" & Price) End Sub It works If we enter the price and the product names populates
Private Sub combo10_AfterUpdate()
Price = DLookup("Price", "LU Product", "Product=" & Combo10)
End Sub
The combo10 is Product field in Form
does not works and gives error 2471

 

[John Big Booty]

I am facing a problem
2 tables LU Product and Sales Point
I want the price from LU product populates when I enter the product in Sales point form
The code I have written is as below
Private Sub Price_AfterUpdate() Product = DLookup("Product", "LU Product", "Price=" & Price) End Sub It works If we enter the price and the product names populates
Private Sub combo10_AfterUpdate()
Price = DLookup("Price", "LU Product", "Product=" & Combo10)
End Sub
The combo10 is Product field in Form
does not works and gives error 2471

This question has been raised in three other threads and has been answered here