Format Function & VBA

[SHAWTY721]

Is there any special syntax that needs to be used when trying to use the Format Function in VBA code.

 

[Remou]

Yes. What seems to be the problem?

 

[SkipVought]

Hi,

Yes, of course. Have you checked VB Help?

How do you intend to use the Format function?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SHAWTY721]

I have a field in my database called CHECK_DT and I want to use the Month portion of the CHECK_DT field for one field and the Year portion for another field that is in the spreadsheet that I am exporting query results to. But when I try to use the Format Function, it's not working. Is the syntax the same as what would be used in Access.

 

[Remou]

You may find that the Month and Year functions suit. Otherwise, please post an example.

 

[SHAWTY721]

WIthin this segment of code is where I was wanting to use the Format Function. I was wanting to use the Year portion of the CHECK_DT field on this line of code:".Cells(J, 4).Value = .Range("G6")" and the Month portion on this line of code:".Cells(J, 7).Value = .Range("G7")

Do Until rst.EOF
    With wbk.Sheets("JournalEntryTemplate")
    IRecords = IRecords + 1
        .Range("G3") = rst.Fields("BranchNumber").Value               
        .Cells(J, 11).Value = rst.Fields("GL_Acct").Value             
        .Cells(J, 12).Value = rst.Fields("GL_Subacct").Value         
        .Cells(J, 15).Value = rst.Fields("GROSS").Value              
        .Cells(J, 17).Value = rst.Fields("AccountDescription").Value 
        .Cells(J, 3).Value = IRecords                                 
        .Cells(J, 4).Value = .Range("G6") 'Year(Now())                
        .Cells(J, 5).Value = .Range("G4")                             
        .Cells(J, 6).Value = .Range("G5")                            
        .Cells(J, 7).Value = .Range("G7") 'Month(Now()) - 1            
        .Cells(J, 8).Value = .Range("G8")                             
        .Cells(J, 9).Value = rst.Fields("BranchNumber").Value         
        .Cells(J, 10).Value = .Range("G3") & "" & ("600")             
        .Cells(J, 18).Value = .Range("G10")                           
        .Cells(J, 2).Value = ("A")                                   
        .Cells(J, 20).Value = rst.Fields("Ins").Value                 
        .Cells(J, 21).Value = rst.Fields("Tax").Value                
        .Cells(J, 22).Value = rst.Fields("Sub").Value                 'Sub
 
    End With

J = J + 1       'Moves to next row each time through loop
rst.MoveNext
Loop

 

[PHV]

Like this ?
.Cells(J, 4).Value = Year(rst.Fields("CHECK_DT").Value)
.Cells(J, 7).Value = Month(rst.Fields("CHECK_DT").Value)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

How are you using the function?

You realize that in Excel you can Format > Cells... and for Real Dates, FORMAT the DISPLAY to show YEAR or MONTH or anything else.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Alruk]

Im not sure if this is your problem but Ive found that if you use the:

Format(Date,"mm") it returns the minute of the Date/Time.

Try using Format(Date,"MM") the upper case forces it to the Month.