Ошибка преобразования типа данных 3421

• 
• VBForums
• Visual Basic
• Office Development
• VBA error: Run-time error 3421: Data type conversion error.

1. Jul 4th, 2014, 09:48 AM

   #1

   

   Thread Starter

   
   Junior Member
   

   

   ---

   VBA error: Run-time error 3421: Data type conversion error.
   

   The whole day i’m strugeling to solve this issue but it seems i can’t come any further with this

   In Access i have made a report with a button that has to open a e-mail message and insert the filtered data of the training days.

   But the problem is i can’t do this because the 2 parameters from the query that is linked to the report are blocking it. And i used the vba code below that should be working but it does not and it gives the following error message:

   
   VBA error Run-time error 3421: Data type conversion error.

   And the following line is highlighted in yellow:

   qdf.Parameters(«[van: dd-mm-yy]») = Format(InputBox(«van: dd-mm-yy», «Start Date», Format(Date, «dd-mm-yy»)), «#mm/dd/yyyy#»)

   The SQL query that is linked with the report is as follow

   Code:

   PARAMETERS [van: dd-mm-yy] DateTime, [tot: dd-mm-yy] DateTime;
   SELECT TrainingCalender.Title, TrainingCalender.Location, TrainingCalender.[Start Time], TrainingCalender.[End Time], TrainingCalender.Description, [Training description].[Training description]
   FROM TrainingCalender LEFT JOIN [Training description] ON TrainingCalender.Title = [Training description].Title
   WHERE (((TrainingCalender.[Start Time])>=[van: dd-mm-yy]) AND ((TrainingCalender.[End Time])<=[tot: dd-mm-yy]));

   And below you can see the VBA code i’m using:

   Code:

   Private Sub Command18_Click()
       Dim objOutlook As Outlook.Application
       Dim objOutlookMsg As Outlook.MailItem
       Dim objOutlookRecip As Outlook.Recipient
       Dim objOutlookAttach As Outlook.Attachment
       Dim qry As QueryDef
       Dim rst As Recordset
   
       Dim db As DAO.Database
       Dim qdf As DAO.QueryDef
       Dim prm As Parameter
   
       ' Create the Outlook session.
       Set objOutlook = CreateObject("Outlook.Application")
   
       ' Create the message.
       Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   
       With objOutlookMsg
           ' Add the To recipient(s) to the message.
           Set objOutlookRecip = .Recipients.Add("person@mail.com")
           objOutlookRecip.Type = olTo
   
           ' Add the CC recipient(s) to the message.
           Set objOutlookRecip = .Recipients.Add("Michael Syama")
           objOutlookRecip.Type = olCC
   
           ' Add the BCC recipient(s) to the message.
           Set objOutlookRecip = .Recipients.Add("Andrew Fler")
           objOutlookRecip.Type = olBCC
   
           ' Set the Subject, Body, and Importance of the message.
           .Subject = "Training dates"
   
           .BodyFormat = olFormatHTML
   
           .HTMLBody = "<p><strong>Dear Colleague,</strong></p>" & _
                       "Welcome to our we updated training curricula as well as a much better training infrastructure." & _
                       "<p>Please see the attachment for the dates! </p>" & _
                       "<b><strong>With Kind regards,</strong></b>" & _
                       "<p> Name <br> </p>" & _
                       "<b> Below you can see the upcoming training schedule: </b><br> "
   
           Set db = CurrentDb
           Set qdf = db.QueryDefs("TrainingCalender Query")
           qdf.Parameters("[van: dd-mm-yy]") = Format(InputBox("van: dd-mm-yy", "Start Date", Format(Date, "dd-mm-yy")), "#mm/dd/yyyy#")
           qdf.Parameters("[tot: dd-mm-yy]") = Format(InputBox("tot: dd-mm-yy", "End Date", Format(Date, "dd-mm-yy")), "#mm/dd/yyyy#")
           Set rst = qdf.OpenRecordset()
   
           Do Until rst.EOF
               .HTMLBody = .HTMLBody & " <TABLE BORDER=4 RULES=NONE FRAME=BOX> " & " " & _
                           " <br>  <p> " & " Training: " & rst![Title] & " " & " <br> " & _
                           " Start Time: " & rst![Start Time] & " " & " <br> " & " Place: " & rst![Location] & " </table> "
               rst.MoveNext
           Loop
           .Importance = olImportanceHigh  'High importance
   
           ' Add attachments to the message.
           If Not IsMissing(AttachmentPath) Then _
               Set objOutlookAttach = .Attachments.Add("L:PublicAccessPDFTrainingen.pdf")
   
           ' Resolve each Recipient's name.
           For Each objOutlookRecip In .Recipients
               objOutlookRecip.Resolve
           Next
   
           ' Should we display the message before sending?
           If DisplayMsg Then
               .Display
           Else
               .Save
               .Display
           End If
       End With
       Set objOutlook = Nothing
   End Sub

   I really hope someone can help me out here because my head is exploding right now

   ---

2. Jul 4th, 2014, 10:40 AM

   #2

   Re: VBA error: Run-time error 3421: Data type conversion error.
   

   the inputbox will return a string (if entered correctly then formatted as dd-mm-yy) if you want to put this into a datetime parameter you need to cast it to a date. the transfer into #mm/dd/yyyy# format should not be required.

   ---

3. Jul 7th, 2014, 03:49 AM

   #3

   

   Thread Starter

   
   Junior Member
   

   

   ---

   Re: VBA error: Run-time error 3421: Data type conversion error.
   

   Originally Posted by digitalShaman
   

   the inputbox will return a string (if entered correctly then formatted as dd-mm-yy) if you want to put this into a datetime parameter you need to cast it to a date. the transfer into #mm/dd/yyyy# format should not be required.

   So it would be something like this?

   qdf.Parameters(«[van: dd-mm-yy]») = Format(InputBox(«van: dd-mm-yy», «Start Date», Format(Date, «dd-mm-yy»)

   qdf.Parameters(«[tot: dd-mm-yy]») = Format(InputBox(«tot: dd-mm-yy», «End Date», Format(Date, «dd-mm-yy»)

   ---

4. Jul 7th, 2014, 04:20 AM

   #4

   

   Thread Starter

   
   Junior Member
   

   

   ---

   Re: VBA error: Run-time error 3421: Data type conversion error.
   

   no that does not work can you give me a a sample?

   ---

5. Jul 7th, 2014, 07:22 AM

   #5

   

   Thread Starter

   
   Junior Member
   

   

   ---

   Re: VBA error: Run-time error 3421: Data type conversion error.
   

   Originally Posted by Arcadia01
   

   no that does not work can you give me a a sample?

   I fixed it it:

   qdf.Parameters(«[tot: dd-mm-yy]») = Format(InputBox(«tot: dd-mm-yy», «End Date»), «dd/mm/yyyy»)

   ---

6. Jul 7th, 2014, 12:19 PM

   #6

   Re: VBA error: Run-time error 3421: Data type conversion error.
   

   i think a
   qdf.Parameters(«[tot: dd-mm-yy]») = Cdate(InputBox(«tot: dd-mm-yy», «End Date»))

   should have also worked as long as the entry is a valid date.

   ---

• 
• VBForums
• Visual Basic
• Office Development
• VBA error: Run-time error 3421: Data type conversion error.

Posting Permissions