SQLServer gone...Hello Access...but... 1

[bluenoser337]

Trying to convert a VB6 application where a date-range report is requested from a SQLServer DB. The syntax is "CrystalReport.SQLQuery = "select * from....". The new report is an Access 2000 report querying an access database based on the date-range selected. What would the code be to call this report. I know how to call a pre-configured report from Access (objAccess etc)...but this throws the "date" variables into the picture...and I'm lost. Any assistance greatly appreciated!

 

[glalsop]

Is objAccess of type Access.Application? If so, and you are using:

objAccess.DoCmd.OpenReport

then you can use the WhereCondition parameter at the end of the OpenReport method:

Dim objAccess As New Access.Application

With objAccess
    .OpenCurrentDatabase ("C:\db1.mdb")

    .DoCmd.OpenReport ReportName:="Report1", View:=acViewPreview, WhereCondition:="MyField=1"
    
    .Visible = True
    .UserControl = True
    
End With

-Gary

 

[bluenoser337]

I am using objAccess.DoCmd.OpenReport. Can it be this easy? Can't wait to get back to the office to try it. I'll report back!

 

[bluenoser337]

Works great!! Awesome...I was looking for something more difficult...like the original SQLServer application. Thanks glalsop!!!!

 

[bluenoser337]

Uh Oh...everything was great until I tried date compares. The database stores the date and time that a part was manufactured as "mm/dd/yyyy h:mm:ss AM" format. In my WhereCondition, it doesn't seem to matter what I enter, it prompts me for ProdDate (the DB column). Sometimes, I need to extract records for the report based on the date-only portion of the ProdDate field. In an Access query I can do this with "int(ProdDate) As DateOnly... In the VB code, I need to do this "int(ProdDate) = gTodaysDate" for example...but it doesn't work. BTW, gTodaysDate is a global variable derived from "Format(Now(), "mm/dd/yyyy"). I was doing this in SQLServer with "where datepart(m,proddate)...but this doesn't apply to Access.

 

[bluenoser337]

Note: ProdDate field is Date/Time data type "General Date" formnat.

 

[glalsop]

Could you post exactly what you are trying to put in the Where condition?

Are you casting to int in order to ignore the time portion of the date/time field? I don't think that you need to do this, if you have a date/time field in Access and pass it only the date portion, Access is smart enough to collect the relevant records.

-Gary

 

[bluenoser337]

.DoCmd.OpenReport ReportName:="My_Report", View:=acPreview, WhereCondition:="int(TableName!ProdDate) = '" & gTodaysDate & "' "

Now, I get prompted for the TableName!ProdDate. I can get what I want with a straight Access query (in Access) with no problem. In the VB syntax, I think I need more ', ", &, or # somewhere.
Thanks!

 

[bluenoser337]

If I use...
WhereCondition:=" ProdDate = '" & gTodaysDate & "', I get prompted for ProdDate (which is a valid name for the DB field of Date/Time data type, I get prompted for this value when I call the report. If I replace this with any other field name in the table, it doesn't prompt (example"WhereCondition:=" color = '" & gTodaysDate & "')??!!

 

[glalsop]

To pass a date, you should surround it with pound signs instead of single quotes.

The fact that you are being prompted for ProdDate suggests that Access doesn't recognize it as a valid field name. Is this field on the report?

-Gary

 

[bluenoser337]

Actually, no...this field isn't on the report. You may be on to something. What exactly does the WhereCondition look at? The report is already preconfigured in Access, and if opened from Access, it will display every record in the table, grouped and sorted exactly as I need them. If I open it from VB with "Length = 12", it returns all the records in the table where length = 12...again grouped and sorted as in the original report. What I want to do is display records based on a date range...with the same grouping, etc. but if "ProdDate" not being in the preconfigured report structure is my problem, how can I get around that? Thanks for the help so far!

 

[bluenoser337]

...and where is the info on things like the "WhereCondition". I have the Access book, the VB6 book, and the ADO book...but none of them cover these "instructions". There's a void in my office...and in my brain.

 

[glalsop]

If the record source for the report is a query, make sure ProdDate is selected in the query.

Ultimately, you need to make sure that ProdDate is available in the record set that serves as your report's record source, be it a table or a query. If you click View -> Field List from the report's design view you should see ProdDate as one of the available fields.

If you don't see it, you need to add it. If it is there and you are still being prompted, try adding the field to the report and setting its visible property to false since you aren't interested in seeing it.

You can find information on The OpenReport method and all of it's arguments in Access help.

-Gary

 

[bluenoser337]

Getting closer...If I put the date variable (derived from "int(ProdDate)" used in the Access query for the report, into the VB code I can get the report for the current day...(WhereCondition:" DateOnly = '" & gDateToday & "' "). Now I can't figure out how to do the math to get "other days" or date ranges.

 

[bluenoser337]

DateOnly" is from the query "Int(ProdDate) as DateOnly" as the DB stores the date and time in general format with each record and I need to compare dates only...usually. I need to have a single date input from a calendar, or date range from 2 calendars (MonthView) for use in the WhereCondition. I've figured out that I can do the DateAdd to today's date and use the resulting value in place of the gDateToday to get a specific date...but I haven't figured out the range thing yet. I also need to produce a report for "all blue parts between 10/02/2004 2:19:13 PM and Now" as the system stores the date and time when the blue parts run was started...which may have been 3 days ago...so in that case I'll need the time, too. Thanks for the help so far!