Query Criteria and Data Formats

[Guest]

I have a query against an ODBC VFP table linked to our sales order software.
I have run the total sum to display a daily total of all sales orders
entered, but need to have the data displayed not only by day but also by week
and month. I cannot find the correct expression for the query or report to
group by week and month as well as by day. Does anyone know how to do this?

PS I posted this about 2-1/2 hours ago and it seemed to be dropped from this
thread, anyone know why?

 

[Albert D. Kallal]

I have a query against an ODBC VFP table linked to our sales order
software.
I have run the total sum to display a daily total of all sales orders
entered, but need to have the data displayed not only by day but also by
week
and month. I cannot find the correct expression for the query or report to
group by week and month as well as by day. Does anyone know how to do
this?

I would fire up the query builder and add a few new columns that have the
month, day , weekday etc that you need for your report.

Thus add all the columns you need from the table into the query grid.

Now, let's add a few more Columns that you will need.

you can type these expressions for each new column directly into the query
builder as follows

myMonth: month[MyDateField])

and for week you can go

myWeek: cint(format([MyDateField],"WW"))

and, for day

day: day([MyDateField]

I placed a command to convert the format "WW" command to a integer via the
cint() function. I done this just in case you want to sort on your report.
(the format command is likely returning a text string and that will not sort
correctly, hence, the cint() command)

So, simply build and save the above query. now simply use the above query
for the data source of your report.

At this point you can use the group by options of the report wirter. In fact
why not use the report wizard for the report. It has been a while but I
believe the report writer and wizard will actually build the month
expressions for you (regardless, if you add the above expressions to the
query builder, then you wil have defined columns as month, day etc for use
in the Report writer.

 

[Guest]

Thanks so much-I'm on the right track now! Can you please advise what the
formula would be to convert the week number into the date of the first day of
that week? For example:

This is week 45. The first day was Sunday, first date of the week is
Nov-4-2007.

Kristi

I have a query against an ODBC VFP table linked to our sales order
software.
I have run the total sum to display a daily total of all sales orders
entered, but need to have the data displayed not only by day but also by
week
and month. I cannot find the correct expression for the query or report to
group by week and month as well as by day. Does anyone know how to do
this?

I would fire up the query builder and add a few new columns that have the
month, day , weekday etc that you need for your report.

Thus add all the columns you need from the table into the query grid.

Now, let's add a few more Columns that you will need.

you can type these expressions for each new column directly into the query
builder as follows

myMonth: month[MyDateField])

and for week you can go

myWeek: cint(format([MyDateField],"WW"))

and, for day

day: day([MyDateField]

I placed a command to convert the format "WW" command to a integer via the
cint() function. I done this just in case you want to sort on your report.
(the format command is likely returning a text string and that will not sort
correctly, hence, the cint() command)

So, simply build and save the above query. now simply use the above query
for the data source of your report.

At this point you can use the group by options of the report wirter. In fact
why not use the report wizard for the report. It has been a while but I
believe the report writer and wizard will actually build the month
expressions for you (regardless, if you add the above expressions to the
query builder, then you wil have defined columns as month, day etc for use
in the Report writer.

 

[Guest]

Thanks so much-I'm on the right track now! Can you please advise what the
formula would be to convert the week number into the date of the first day of
that week? For example:

This is week 45. The first day was Sunday, first date of the week is
Nov-4-2007.

Kristi

I have a query against an ODBC VFP table linked to our sales order
software.
I have run the total sum to display a daily total of all sales orders
entered, but need to have the data displayed not only by day but also by
week
and month. I cannot find the correct expression for the query or report to
group by week and month as well as by day. Does anyone know how to do
this?

I would fire up the query builder and add a few new columns that have the
month, day , weekday etc that you need for your report.

Thus add all the columns you need from the table into the query grid.

Now, let's add a few more Columns that you will need.

you can type these expressions for each new column directly into the query
builder as follows

myMonth: month[MyDateField])

and for week you can go

myWeek: cint(format([MyDateField],"WW"))

and, for day

day: day([MyDateField]

I placed a command to convert the format "WW" command to a integer via the
cint() function. I done this just in case you want to sort on your report.
(the format command is likely returning a text string and that will not sort
correctly, hence, the cint() command)

So, simply build and save the above query. now simply use the above query
for the data source of your report.

At this point you can use the group by options of the report wirter. In fact
why not use the report wizard for the report. It has been a while but I
believe the report writer and wizard will actually build the month
expressions for you (regardless, if you add the above expressions to the
query builder, then you wil have defined columns as month, day etc for use
in the Report writer.

 

[Albert D. Kallal]

Thanks so much-I'm on the right track now! Can you please advise what the
formula would be to convert the week number into the date of the first day
of
that week? For example:

This is week 45. The first day was Sunday, first date of the week is
Nov-4-2007.

[yourDate] - weekday([YourDate]) + 1

Simply use the day of week (weekday), and subtract what day of the week..and
then + 1.....