Visual Basic Forum for Visual Basic Programmers VB

[xtine_m]

I'm kinda new in VB and I am making my own database for my work. I used this code in the Data Environment:

"SELECT Item_Code, SUM(Quantity) AS QTY, Description, SUM(Total) AS Expr1 FROM Purchase GROUP BY Item_Code, Description ORDER BY Description"

My problem is that it sum all the purchase from the very beggining down to the latest. I wanted to choose the dates that it would add and not all of it. Can you please help me?

[dougthomas]

Hi There,

You need to put a WHERE clause into your SQL. Something like:

"SELECT Item_Code, SUM(Quantity) AS QTY, Description, SUM(Total) AS Expr1 FROM Purchase WHERE Date_Purchased BETWEEN 'date1' AND 'date2' GROUP BY Item_Code, Description ORDER BY Description"

Hope this helps

Regards
Doug

[xtine_m]

Hi! I tried this one

"SELECT Item_Code, SUM(Quantity) AS QTY, Description, SUM(Total) AS Expr1 FROM Purchase WHERE Date_Purchased BETWEEN '11/05/04' AND '12/30/04' GROUP BY Item_Code, Description ORDER BY Description"

but it would give out the error date_purchased has no default value. What does it mean?

Thanks!

[dougthomas]

Sorry, I didn't make it clear in my initial response,

You want to sum the values depending upon a date range.

I used "Date_Purchased" as an example. Replace "Date_Purchased" with whatever the name you have in the table representing the Date for which you want to sum.

Regards
Doug

[xtine_m]

Hi! Thanks! But I tried it in Data Environment but it did not work. Do you think I should connect it with a text box?

[dougthomas]

Hi again,

Please could you be more specific ? When you tried it, what exactly didn't work ?

An example of the actual SQL you are using and environment would help.

Regards
Doug

[xtine_m]

well actually there's no error message but the drop down in the command will be gone.

" To retrieve field information for this command, the command must be executed. You may need to specify input parameter values in the Parameters tab before the command is executed. Execution may add to or modify data in the database

Execute command? yes or no"

I typed this one:

"SELECT Item_Code, SUM(Quantity) AS QTY, Description, SUM(Total) AS Expr1 FROM Purchase where date between '11/04/04' to '12/30/04' GROUP BY Item_Code, Description ORDER BY Description"

Do you have an email? I can send you my VB project if you want.

[dougthomas]

Hi,

I've sent you a private message with my e-mail address

Regards
Doug

[MadhuDevi]

It is impossible to connect the data from the textbox to the data environment. You should give the data directly to extract the data.

Only through the programming, you can select the data and bring it to the report.

Are you are going to make to report from this data environment.

You reply for this message.
_________________
I am very much interested in doing programs in VB with access as backend

[xtine_m]

Hi! Can you please elaborate? I don't seem to get your question. Thanks!

[dougthomas]

Hi again. It's me,

Finally got round to looking at your problem again. The main issue i had was that I have only got Access 97 on this PC so I had to move all your stuff onto another one. The other problem was that I'm not familar with Data Environment !

Anyway all that apart, it looks like a fairly simple thing (and it's my fault).

I modified the Command1 CommandText in the Data Environment to:

SELECT Item_Code, SUM(Quantity) AS QTY, Description, SUM(Total) AS Expr1 FROM Purchase WHERE Date BETWEEN #11/04/04# AND #12/30/04# GROUP BY Item_Code, Description ORDER BY Description

Note the use of the "#" character around the dates rather than single or double quotes. I should have seen that earlier - how stupid am I ??

I cut and pasted it into an Access Query within your Database, and it worked OK. (at least it came up with different results than it did without the WHERE Clause)

By the way you've got an error in Function Checkvalues, the "vbCritical" on the MSGBOX has lost its "+".

Hope this helps you

Regards
Doug

[dougthomas]

Hi again,

Think I've got a solution for the Date Selection. (I'm not sure if it's the *best* solution 'cause I don't know much about Data Environment)

Here goes.

(1) Put a couple of Parameters into the Monthly Data Member's SQL:

SELECT Item_Code, SUM(Quantity) AS QTY, Description, SUM(Total) AS TOT FROM Purchase WHERE Date BETWEEN strStartDate AND strEndDate GROUP BY Item_Code,Description ORDER BY Description

(Note the change "SUM(Total) as TOT" not "SUM(Total) as Total" as you had - Access reported a circular reference when I ran the query. You'll have to change the reference to Total to TOT in DataReport2 as well)

The Parameters are 'strStartDate' and 'strEndDate'. When you change the SQL statement click on the Parameters Tab and make sure that strStartDate and strEndDate are defined as Input Parameters, Type dbSTR and Values set to strStartDate and strEndDate respectively. Also, specify the Database values in the Parameter definition as string.

(2) Now all you have to do is assign values to the Parameters (this is the bit I'm not too keen on):
I've modified your date_to code to look like: