UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Docmd.runsql Statement Not Correct?, Access 2016    
 
   
jackjsmith88
post Jun 25 2019, 04:19 AM
Post#1



Posts: 45
Joined: 28-October 18



Hi All,

Designing a report, that takes snippets of information from different queries, and displays it all on one report. i've recently learned, that it would of probably been easier to use and learn subreports and subforms, but.. i've got so far and can't be arsed to change it or spend another 10 + hours learning about substuff.

So, when the form opens/loads, i want various labels captions to change to values reflected in a query.

Here is a SQL/VBA statement.

Private Sub Report_Open(Cancel As Integer)

Dim SQL As String

SQL = "SELECT Date/BrandTotal.Silestone FROM Date/BrandTotal"

Me.SilestoneTotal.Caption = SQL

I want the caption to show the value pulled from that field in the SQL statement.

What am i doing wroooooong?

Thanks in advance

End Sub
Go to the top of the page
 
cheekybuddha
post Jun 25 2019, 04:49 AM
Post#2


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


>> What am i doing wroooooong? <<

Using non-alpha-numeric characters in your table/object names.

You **can** do it, but you must remember to enclose any object names containing spaces/silly characters in square brackets which is a pain!
CODE
SQL = "SELECT [Date/BrandTotal].Silestone FROM [Date/BrandTotal]"


You'd be better off renaming the table IMHO.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 25 2019, 04:52 AM
Post#3


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


>> I want the caption to show the value pulled from that field in the SQL statement. <<

Ah!

Does your table [Date/BrandTotal] only have one record?

If not, you will need a WHERE clause to get the single row value you need

So you need a function to get the value from the SQL:
CODE
Me.SilestoneTotal.Caption = Nz(DLookup("Silestone", "[Date/BrandTotal]"), "Unknown")


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
jackjsmith88
post Jun 25 2019, 04:54 AM
Post#4



Posts: 45
Joined: 28-October 18



Thanks very much david!

Much appreciated!
Go to the top of the page
 
cheekybuddha
post Jun 25 2019, 05:38 AM
Post#5


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


yw.gif

You didn't answer the question about whether the table has more than one record and, if so, whether you need to determine which record should provide the Silestone value.

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Jun 25 2019, 06:30 AM
Post#6


UtterAccess VIP
Posts: 7,898
Joined: 24-May 10
From: Downeast Maine


SQL is a reserved word. You would do better to use strSQL or similar as the variable name. But as David pointed out, you will need a calculation to retrieve the value. SQL in the context of your code is just the SQL statement itself.
Go to the top of the page
 
jackjsmith88
post Jun 25 2019, 06:58 AM
Post#7



Posts: 45
Joined: 28-October 18



Sorry, Busy today!

IU am intrigued by your question though and also the methods behind both.

So, The Query that this all points to, returns 1 row of information.

The query is 4 expressions, a sum field, and a where clause.

the expressions are just totals of numerical fields by brand. the sum field is total slabs, in between a date range entered by a user on a little form.

So, i tried the NZ(Dlookup()) that you gave and it worked perfectly for the 4 expressions. However, not for the Sum field.

I did some looking around and saw Dsum() but i'd just be adding a sum to a sum. i thought about CInt, but its already an integer. but yet declared as a String in the Private Sub.

What am i doing wrong?

i love to learn the why's and wherefores behind answers, so if you have the time to explain what to use and why, then i'd greatly appreaciate it :-)
Go to the top of the page
 
cheekybuddha
post Jun 25 2019, 07:00 AM
Post#8


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


So [Date/BrandTotal] is a query?

I suppose it would help to see the underlying SQL of the query.

Since it only returns one row, then generally you are fine.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
jackjsmith88
post Jun 25 2019, 07:04 AM
Post#9



Posts: 45
Joined: 28-October 18



Hi Bruce,

Thanks for the response,

How would one add the calculation to the SQL?

My knowledge of Both SQL and VBA is basic, i'm grasping the basics of both languages,

where i struggle is how to apply them both together, for example when using a docmd.runsql in VBA. i always get compile errors

basically what i'm trying to do is get a labels caption to change based on the value pulled from a query.

The first 4 captions work fine with the NZ(Dlookup) functions.

The 5th caption wont work presumably because it is a Summed field in query.

i even tried adding the 4 x Dlookups together (would return the same number) but in the caption it just added them nedxt to each other. "2446170" instead of adding together 24+46+17+0 to show the value "87"

Any ideas you have matey?

Go to the top of the page
 
jackjsmith88
post Jun 25 2019, 07:04 AM
Post#10



Posts: 45
Joined: 28-October 18



SELECT Sum([WeeklyTransportReport]![Silestone12mm]+[WeeklyTransportReport]![Silestone20mm]+[WeeklyTransportReport]![Silestone30mm]) AS Silestone, Sum([WeeklyTransportReport]![Dekton04mm]+[WeeklyTransportReport]![Dekton08mm]+[WeeklyTransportReport]![Dekton12mm]+[WeeklyTransportReport]![Dekton20mm]+[WeeklyTransportReport]![Dekton30mm]) AS Dekton, Sum([WeeklyTransportReport]![Sensa/Granite20mm]+[WeeklyTransportReport]![Sensa/Granite30mm]) AS Sensa, Sum([WeeklyTransportReport]![Sink51x41]+[WeeklyTransportReport]![Sink34x37]+[WeeklyTransportReport]![Sink51x37]) AS Sinks, Sum(WeeklyTransportReport.TotalSlabs) AS SumOfTotalSlabs
FROM WeeklyTransportReport
WHERE (((WeeklyTransportReport.DateofSale) Between [Forms]![QryDateParameterSTATS]![QryStartDate] And [Forms]![QryDateParameterSTATS]![QryEndDate]));
Go to the top of the page
 
jackjsmith88
post Jun 25 2019, 07:08 AM
Post#11



Posts: 45
Joined: 28-October 18



As soon as i put the SQL code in front of me i saw the problem.

Totalslabs after the query runs is transformed to Sumoftotalslabs, as soon as i put that in NZ(Dlookup()) it worked, i pointed it to the wrong field!
Go to the top of the page
 
cheekybuddha
post Jun 25 2019, 07:17 AM
Post#12


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

Great that you worked it out!

On a separate note, you may wish to look at the UA beginner's reading links with regards to database structure.

The field names you have start to ring alarm bells, and you may find certain aspects of getting the information you want from your db more difficult down the road.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
jackjsmith88
post Jun 25 2019, 07:32 AM
Post#13



Posts: 45
Joined: 28-October 18



everything about this database rings alarm bells lol, its very much an amateur build.
Go to the top of the page
 
BruceM
post Jun 25 2019, 08:25 AM
Post#14


UtterAccess VIP
Posts: 7,898
Joined: 24-May 10
From: Downeast Maine


QUOTE
How would one add the calculation to the SQL?

You can have a simple query "SELECT LastName FROM tblEmployee WHERE EmployeeID = 987", but the string itself (the value of the string variable) is not going to reveal any LastName value. To get that information you need to process the data that results from executing the SQL.

Adding the calculation to the SQL is not this issue to which I referred, but rather that the variable SQL is a string (text) variable that consists of the literal SQL statement. To get the result from the SQL statement you need to process it. One way is with DLookup, which is what it appears you are doing. You could also open a recordset based on the SQL statement, and extract the value that way. It accomplishes pretty much the same thing as DLookup, but is more efficient if the recordset is large. If DLookup is working, I would stay with that for now.

I will repeat both the congratulations for working out the answer, and the suggestion to click the Newcomer's Reading List link on the UtterAccess home page. Crystal's tutorial is as good a starting point as any, but there are a lot of good articles in there.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th July 2019 - 03:28 AM