Getting Date field into Database

[ericm]

Ok this is probably not going to be my last one but anyways

I am trying to have the test date inserted into my database theough odbc and keep getting a syntax error

Insert into hm (TestName, Folder, Date, Alive, Dead, Average) VALUES ('%TestName%', '%Folder%',%Date%,left('%AliveRatio%',instr('%AliveRatio%',' %')-1) ,left('%DeadRatio%',instr('%DeadRatio%',' %')-1) ,'%AverageReply%')

I have tried setting the data field in the DB to Date and Text

[Yoorix]

Insert into hm (TestName, Folder, Date, Alive, Dead, Average) VALUES ('%TestName%', '%Folder%',%Date%,left('%AliveRatio%',instr('%AliveRatio%',' %')-1) ,left('%DeadRatio%',instr('%DeadRatio%',' %')-1) ,'%AverageReply%')

For Access database you should use field data type: Date/Time, Date format : full date format and %DateTime% variable.

Also you shoud wrap %DateTime% variable in ', e.g:
Insert into hm (TestName, Folder, Date, Alive, Dead, Average) VALUES ('%TestName%', '%Folder%','%DateTime%',left('%AliveRatio%',instr('%AliveRatio%',' %')-1) ,left('%DeadRatio%',instr('%DeadRatio%',' %')-1) ,'%AverageReply%')

Regards,
Yoorix

[ericm]

Also you shoud wrap %DateTime% variable in ', e.g:
Insert into hm (TestName, Folder, Date, Alive, Dead, Average) VALUES ('%TestName%', '%Folder%','%DateTime%',left('%AliveRatio%',instr('%AliveRatio%',' %')-1) ,left('%DeadRatio%',instr('%DeadRatio%',' %')-1) ,'%AverageReply%')

That was a typo in my post. I double checked and I do have the %DateTime% wrapped.

I have tried every possible setting in both the database and the hostmonitor. Although in access I do not have full date format. I have Long, Short, and General. Plus a few others I know wouldn't apply but I tried them anyways.

Befor the question comes.
OS Name Microsoft Windows 2000 Server
Version 5.0.2195 Service Pack 4 Build 2195
OS Manufacturer Microsoft Corporation
System Manufacturer Dell Computer Corporation
System Model PowerEdge 1750
System Type X86-based PC
Processor x86 Family 15 Model 2 Stepping 9 GenuineIntel ~2388 Mhz
Processor x86 Family 15 Model 2 Stepping 9 GenuineIntel ~2388 Mhz
Processor x86 Family 15 Model 2 Stepping 9 GenuineIntel ~2388 Mhz
Processor x86 Family 15 Model 2 Stepping 9 GenuineIntel ~2388 Mhz
BIOS Version Phoenix ROM BIOS PLUS Version 1.10 A05
Windows Directory C:\WINNT
System Directory C:\WINNT\system32
Boot Device \Device\Harddisk0\Partition2
Locale United States
Time Zone Eastern Daylight Time
Total Physical Memory 2,096,624 KB
Available Physical Memory 1,176,264 KB
Total Virtual Memory 4,557,704 KB
Available Virtual Memory 2,902,800 KB
Page File Space 2,461,080 KB
Page File C:\pagefile.sys
Host Monitor 5.92
Access 2002

[Yoorix]

I have tried every possible setting in both the database and the hostmonitor. Although in access I do not have full date format. I have Long, Short, and General. Plus a few others I know wouldn't apply but I tried them anyways.

Ok. I have installed Access 2002 on Windows 2003 SP1 but I have localized version of Access (not English). In Date format dropdown you should select 19.06.1994 17:34:23 format. I have selected such format and it works with Hostmonitor like a charm. Also you should try to set "Fixed Date&Time" format option (HM > Options > Misc > Date&Time format)

Regards,
Yoorix

[ericm]

Ok I figured this out by mistake.
In my database the colum name was Date. While trying to figure this out by recreating a new table I mispelled the Date colum to datw and the query worked. When I fixed the mispelling to Date it didn't work again.

So for some reason the query will not execute if the Colum Name is Date.

I now renamed it to TestDate and it works fine.

[KS-Soft]

"Date" is reserver word, you should not use such name as field name.

Regards
Alex