Data type mismatch?

[odin1701]

I don't understand why this won't work.

I have a date field in a table. The default value for this field is Date().

The data type is Date/Time

The field is called Import Date.

Here is what I am trying to do in a query:

Month Imported: DatePart('m', 'Import Date')

I am getting a data type mismatch, and I don't see how.

If I put this:

Month Imported: DatePart('m', Date())

It works fine.

 

[pbaldy]

Because you've given it a string, not a date. Try:

Month Imported: DatePart('m', [Import Date])

 

[odin1701]

erm....oops lol

I'm going home

 

[gobeav3rs297]

Also have a data type mismatch issues so i might as well post in this thread instead of creating another thread. Anyway, i did this quiery msxFirst: Function([FirstName],' ',' ') where [FirstName] is the string from the column FirstName, but when i tried to run the quiery as recieved the type mismatch error. I debug and traced it to this line of code in my module: nLength = UBound(pWord) where aLength is an integer and pWord is a array that hold the FristName value passed in. What am i doing wrong? I'm not very experienced w/ VB or Access so if my question is confusing sorry about that.

Vince

 

[CraigDolphin]

My guess is that you're encountering a problem with a null value where the data type doesn't allow one. Check your table for nulls in the FirstName field. You could avoid propagating the null by using the Nz() function depending on what output you are after...

msxFirst: Function(Nz([FirstName],"NameToPassIfNull"),' ',' ')

Otherwise I think folks will need to see the code in your 'Function' to help

 

[gobeav3rs297]

There is no Null values in the field, it is all filled. Here is an excerpt up to where the debugger pointed out the issue. I'm guessing that pWord which holds the FirstName value is not an array so when it tries to find the length of the array it coughs up that error. Though i thought that string is pretty much just a an array of chars so it should be fine right? I found this DoubleMetaphone function online and trying to incorporated into my project but since i'm not as familiar with VBA i'm still learning as i go. If anyone can point to as what i'm doing wrong that would be great.



Option Compare Database
Option Explicit
Option Base 0

Public Alternate As Boolean ' Set to True if DoubleMetaphone returns
' two phonetic codes
Public Primary ' Primary phonetic code
Public Secondary ' Secondary phonetic code


Function DoubleMetaphone(ByVal pWord, MetaPh, MetaPh2) As Boolean
'********************************************************************
'* Returns phonetic codes in Metaph (primary) and Metaph2 (secondary)
'* for pWord
'*
'* pWord is an array
'********************************************************************
Dim SIGNIFICANTCHARS As Integer

Dim nLength As Integer, sLetter, nCurrent As Integer, nLast As Integer
Dim PreviousFlag As Boolean, PreviousFlag2 As Boolean

Primary = ""
Secondary = ""

SIGNIFICANTCHARS = 4 ' Can be changed to allow more or less characters returned

nLength = UBound(pWord)
nLast = nLength

 

[Jon K]

i did this quiery msxFirst: Function([FirstName],' ',' ') where [FirstName] is the string from the column FirstName,

[FirstName] is a field containing text strings, not arrays.
.

 

[gobeav3rs297]

Hey Jon,

How then do i pass in that text string from that firstname field as an array into that function?

Vincent

 

[Jon K]

I don't know what the DoubleMetaphone() function does, but I think you can modify its code to first accept the FirstName as text and convert it into an array pWord. Something like...

Public Function DoubleMetaphone(FName As String, MetaPh, MetaPh2) As Boolean
   Dim pWord()
   Dim i As Integer
   
   ReDim pWord(Len(FName) - 1)
   For i = 0 To Len(FName) - 1
      pWord(i) = Left(FName, 1)
      FName = Mid(FName, 2)
   Next i
   
   ' Now you have an array pWord containing the characters of the FName.
   ' You can continue with the original code of the DoubleMetaphone() function.
   
End Function

Hope it helps.
.