Help: Help on Access, how to make Date/Time to just Date?

[alvin777]

Good morning, I just need the Date format on Access but the list of data tye only has Date/Time is there anyway to change ot just Date. I need to change it to date only because when I transfer a recorset to Excel, it inlcudes the time as well which is not needed.

God bless,
Alvin

[Beacon]

Hi Alvin,

After selecting Date/Time as the field type change the Format property of that field to Short Date or whatever.

ciao
b

[alvin777]

Thanks for the reply but it's set like that already. Is there any more data type like a plug-in for Access where there is only the Date and not Date/time?

Any work around for this. I just can't transfer the recordset to Excel with the time on it, it will ruin the print out when the user prints it out (not to mention wasting ink on very big print outs) as the date is the only one needed.

God bless,
Alvin

[swatty]

If it is a recordset your transfering you could just get the date wihtout the time.

Will search the syntax for you.

[Ecniv]

Format the cell in excel to dd mmm yyyy....

Code it in if you want to...


Vince

[si_the_geek]

From Access you can just put a format in your query, eg:

SELECT Format(<DateTimeField>,'dd/mm/yyyy'), ...

[Ecniv]

Si,

If you did that it might put it in excel as text (I did that before) then the sorting in excel won't work right.

Best idea is put it in with date/time. tehn format the Excel cell to the format required.



Vince
(Although I found yesterday that excel is soooooooooo helpful when typing things in cells. . like assuming lots of things for you :/ )

[alvin777]

Thanks again for th reply but how do you format Excel to just have the Date and disregard the time after being trasferred from the recordset.

The program I'm making is not for me. Is there a more automatic way to disregard the Time. They might not want have the Excel's setting changed for their other normal sheets.

How come Access has to put in the the time in date and why doesn't it have just a pure date data type btw?


God bless,
Alvin

[Ecniv]

The date is stored the same way in both excel and access.

Its a number.

If you don't believe me - type a date into excel and change the formatting to general. It should go to a number (unless it classed it as text in which case it won't change).

How are you putting the data into the cells? Manual code or via excels recordset transfer?

If manual code you need to add (or soemthing similar as this is direct from excel):

Selection.NumberFormat = "dd mmm yyyy"

So probably - objExcel.Selection.NumberFormat = "dd mmm yyyy"

If you've transferred out from access or anything then the user can change the formatting and look of the excel sheet - if they can't do this they need to learn how.


Vince

[alvin777]

Thanks again for the replies. I don't really know how to use Excel (I'm not even at the beginner level) application itself.

This is the code I use to transfer the recordset from vb to Excel, most I got from the forum. The dates in Access are date/time short date ( I just wished it just had Date only, to make it easier):

Private Sub mnuFileExcel_Click()
'Check dates first
If IsDate(txtFrom.Text) = False Then
intMsg = MsgBox("Check date if it's correct", vbOKOnly, Empty)
txtFrom.SetFocus
Exit Sub
End If

If IsDate(txtTo.Text) = False Then
intMsg = MsgBox("Check date if it's correct", vbOKOnly, Empty)
txtTo.SetFocus
Exit Sub
End If
Me.MousePointer = 11
'-------------------------------------------------------
'Check first if there is a record to save
Set d = OpenDatabase(App.Path & "\Parts Inventory.mdb")
Set r = d.OpenRecordset("SELECT * FROM [Receiving Table] WHERE Received BETWEEN #" & txtFrom & "# AND #" & txtTo & "#")
r.Requery

'If no record is found
If r.RecordCount = 0 Then
intMsg = MsgBox("There are no records to save yet", vbOKOnly + vbExclamation, Empty)
Me.MousePointer = 0
Exit Sub
End If
'-------------------------------------------------------

Set d = OpenDatabase(App.Path & "\Parts Inventory.mdb")
Set r = d.OpenRecordset("SELECT * FROM [Receiving Table] WHERE Received BETWEEN #" & txtFrom & "# AND #" & txtTo & "#")


Dim x As New Excel.Application

x.Application.DisplayAlerts = False
x.Visible = False

Dim w As Worksheet
x.Workbooks.Add
Set w = x.Worksheets(1)
Set q = w.QueryTables.Add(r, w.Range("A1"))
q.Refresh (True)

On Error GoTo err
x.Workbooks.Application.SaveWorkspace ' ("c:\pibkrecv.xls")
x.Quit
MsgBox "It has been saved", vbInformation, Empty
Me.MousePointer = 0
'x.SaveChanges = False


Set d = Nothing
Set r = Nothing
On Error GoTo exit2

exit2:
Exit Sub
MsgBox "Error", vbInformation, "Not Saved, Excel application may not have been installed"
Me.MousePointer = 0

'Should the user click Cancel in the save dialog box
err:
Exit Sub
Me.MousePointer = 0
End Sub

[Ecniv]

You'd need to make a selection first...

objExcel.Selection.NumberFormat = "dd mmm yyyy"

Is the querytables a fast transfer? I'm asking because I use a loop through the recordset and move to teh cells writting the data in - this is slow though and I would like to know if the querytables is faster... Excel help hasn't been installed here at work so thats no use to me.


Vince

[si_the_geek]

Ecniv, do you write one cell at a time? If you do this is about 10 times quicker (obviously depending on how many columns you are getting)

'rowMin = First row to write to
'num_fields = RecSet.Fields.Count
'tmp_val = Number of rows of data to write
'"field_type" returns Text for any text based field (text/char etc)
'"xlCol" returns the column letter (eg: 1 gives A, 26 gives Z etc)
  start_row = rowMin
  ReDim tmp_xl_array(tmp_val, num_fields)
  tmp_str = ":" & xlCol(num_fields + colMin - 1)
  tmp_str2 = xlCol(colMin)
  Do While Not (RecSet.EOF)
    For I = 0 To num_fields - 1
      If Not (IsNull(RecSet(I))) Then
        If field_type(RecSet(I), RetMajor:=True) = "Text" Then t = "'"
      End If
      tmp_xl_array(rowMin - start_row, I) = t & Trim(CStr("" & RecSet(I)))
    Next I
    RecSet.MoveNext
    rowMin = rowMin + 1
    If rowMin Mod 25 = 0 Then DoEvents
  Loop
  tmp_row = rowMin - start_row - 1
  rowMin = tmp_row + start_row
  .Range((tmp_str2 & CStr(start_row) & tmp_str & CStr(rowMin))) = tmp_xl_array

[Ecniv]

So you can set an array to the range??

Neat - I'll have a go later

Thanks


Vince