date error

[swarv]

I have a SQL statement that places 2 text fields into a record in a table. the text in the form is in date form, when i look at the sql statement it looks like it is putting across the date. but when i look in the table it shows 00:20:04. When in design mode the table says that field is date/time.

Anybody any ideas?
Thanks

 

[DCrake]

What format is the date field? if you have left it blank it will use General Date which includes both the date and time

Suggest you change the format to dd/mm/yyyy
and set the input mask to 99/99/0000;0;_

David

 

[Rabbie]

Dates and times are always stored in Access as Date/time fields. Can you post your actual SQL so we can see what is happening. Remember you need to enclose your date within # chars if it is a literal date.

 

[swarv]

the sql statement i use is:

        SQLText = "INSERT INTO absent ([start_date], [end_date], [days], [reason], [name]) SELECT " & totalstartdate & ", " & totalenddate & ", " & TotalBusinessDays & ", '" & reasonwhy & "', [Text18]"
[\code]

 

[swarv]

in the table both fields are displaying 30th December 1899. Even though the SQL statement is passing 2 different dates to the table.
Any ideas?
Thanks

 

[swarv]

when i change the field type to text the data appears as:
1.39372822299652E-02
ideas?

 

[Rabbie]

It looks like you are only storing the time. Date/Times are stored in Access as a real number. The integer part gives the number of days since 31/12/1899 and the decimal fraction part is the time as a fraction of a day.

Your field value is less than 1 so thats why its showing as 31/12/1899.

How are you populating totalstartdate and totalenddate as these are the values you are storing.?

 

[swarv]

txt_date is the first date text box and txtenddate is the second date text box. These are just normal text boxes. in the VB code it says

dteStart = txt_date
dteEnd = txtEndDate

Dim totalstartdate As Date
Dim totalenddate As Date
    
totalenddate = dteEnd
totalstartdate = dteStart

Is this correct?
Then the sql statement to put the values into the record.

 

[gemma-the-husky]

SQLText = "INSERT INTO absent ([start_date], [end_date], [days], [reason], [name]) SELECT " & totalstartdate & ", " & totalenddate & ", " & TotalBusinessDays & ", '" & reasonwhy & "', [Text18]"

in this statement the variables totalstartdate and totalenddate still have to be wrapped in # characters. its surprising it worked at all. Are you in the UK or US - you also need to watch for dates being recorded as US dates by default - if you are in the UK you may need to force format them to make sure they are treated correctly

 

[swarv]

how do you mean # characters? I am in the UK

 

[Rabbie]

how do you mean # characters? I am in the UK

Both Gemma and I are in the UK. The # on my keyboard is at the righthand end of the second row of letters. Hope this helps

 

[swarv]

i mean, what do you mean by 'wrap it in # characters'?

 

[Rabbie]

We mean put # characters around the date like
SQLText = "INSERT INTO absent ([start_date], [end_date], [days], [reason], [name]) SELECT #" & totalstartdate & "#, #" & totalenddate & #", " & TotalBusinessDays & ", '" & reasonwhy & "', [Text18]"

 

[swarv]

excellent thank you very much