I'm trying to pull information from an Access database, then format it a bit, and push it into a FM8 database as a new record. It works great if I have all fields in the filemaker database set to text... But if I change the field that will hold the date to a 'date' type it only imports the first record, then the SQL engine bombs out on the 2nd query saying the "Query Failed".

I pull from the access database a string, that used to be a date in the actual program that uses it. I then trim off any white space. If I output the date to the screen that crashes it, it's 12/29/2005. Which is valid? Any ideas? Here's the entire script:

' This script is going to query the Alpine ATP, and pull out

' jobtrack information, then push this information into FileMaker 8

'-----------------------------------------------------------

'Setup Constants and Variables

'-----------------------------------------------------------

Dim fso, filesys

Dim thisWeekDate, lastWeekDate

Dim SQLQuery, SQLUpdateQuery

Dim salesman, dealer, jobContact, quoteDate, alpineJobNumber, jobDescription, designer, cost

Dim alpine(1000, 7)

Dim num

thisWeekDate = DATE - 1

lastWeekDate = thisWeekDate - 6

Set fso = CreateObject("Scripting.FileSystemObject")

'-----------------------------------------------------------

'Read in the information from the ATP Database and put into the array

'-----------------------------------------------------------

Set OBJdbConnection = CreateObject("ADODB.Connection")

OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=srs3Alpine Folder2005atpd2005ATP.MDB"

SQLQuery = "SELECT JOB_SMAN_KEY, QUOTE_DATE5, JOB_CUST_KEY, JOB_KEY, JOB_NAME, JOB_COST, JOB_CONTACT, JOB_DESIGNER_KEY FROM JOBHEAD WHERE (QUOTE_DATE5 Between #" + CStr(lastWeekDate) + "# And #" + CStr(thisWeekDate) + "#) ORDER BY JOB_SMAN_KEY, JOB_CUST_KEY;"

Set Result = OBJdbConnection.Execute(SQLQuery)

num = 0

if Not Result.EOF then

Do While Not Result.EOF

if (Not IsNull(Result("QUOTE_DATE5")) And Not IsNull(Result("JOB_CUST_KEY")) And Not IsNull(Result("JOB_KEY")) And Not IsNull(Result("JOB_NAME")) And Not IsNull(Result("JOB_COST")) And Not IsNull(Result("JOB_CONTACT")) And Not IsNull(Result("JOB_DESIGNER_KEY"))) Then

alpine(num, 0) = Replace(Trim(CStr(Result("JOB_CUST_KEY"))), "'", "FT")

alpine(num, 1) = Replace(Trim(CStr(Result("JOB_CONTACT"))), "'", "FT")

alpine(num, 2) = CDate(Result("QUOTE_DATE5"))

alpine(num, 3) = Replace(Trim(CStr(Result("JOB_KEY"))), "'", "FT")

alpine(num, 4) = Replace(Trim(CStr(Result("JOB_NAME"))), "'", "FT")

alpine(num, 5) = Replace(Trim(CStr(Result("JOB_DESIGNER_KEY"))), "'", "FT")

alpine(num, 6) = Replace(Trim(CStr(Result("JOB_COST"))), "'", "FT")

'MsgBox Result("QUOTE_DATE5")

num = num + 1

end if

Result.MoveNext

Loop

end if

OBJdbConnection.Close

num = num - 1

'Open Connect to FM8 JobTrack Database

Set OBJdbConnection = CreateObject("ADODB.Connection")

OBJdbConnection.Open "jobTrack.fp7", "Admin"

Do While (num > -1)

'Push values formatted into FM8 Database

SQLUpdateQuery = "INSERT into jobTrack values ('" & alpine(num, 0) & "', '" & alpine(num, 1) & "', '" & CStr(alpine(num, 2)) & "', '" & alpine(num, 3) & "', '" & alpine(num, 4) & "', '" & alpine(num, 5) & "', '" & alpine(num, 6) & "')"

'MsgBox SQLUpdateQuery

'Execute

OBJdbConnection.Execute SQLUpdateQuery

num = num - 1

Loop

OBJdbConnection.Close

So basically how can I format the date so FM8 wont be a prick and take whats given to it? LOL

I got it.

Wow, that was gay! You can't push it a 4-digit year for some reason. Works great when pushing it 2006, but anything 2005 didn't work.

Here's the work around lines I changed:

alpine(num, 2) = Replace(Replace(Result("QUOTE_DATE5"), "2005", "05"), "2006", "06")

SQLUpdateQuery = "INSERT into jobTrack values ('" & alpine(num, 0) & "', '" & alpine(num, 1) & "', '" & alpine(num, 2) & "', '" & alpine(num, 3) & "', '" & alpine(num, 4) & "', '" & alpine(num, 5) & "', '" & alpine(num, 6) & "')"