Displaying a Query Result in Form field?

[jamiesuperman420]

Hey!!

I'm building a database for a company that does different property maintenance services, and each property is sorted by Code. For each Code there might be more than one entry for Landscaping, for example.

ACF01 - $350
ACF01 - $200

Using a query, I can consolidate that into ACF01 - $550, but I can't use that data anywhere but inside that query.

I'm trying to bring that $550 into a form field for LANDSCAPING TOTAL (of course when displaying the page for ACF01).

Please help!!

 

[jal]

Hey!!

I'm building a database for a company that does different property maintenance services, and each property is sorted by Code. For each Code there might be more than one entry for Landscaping, for example.

ACF01 - $350
ACF01 - $200

Using a query, I can consolidate that into ACF01 - $550, but I can't use that data anywhere but inside that query.

I'm trying to bring that $550 into a form field for LANDSCAPING TOTAL (of course when displaying the page for ACF01).

Please help!!

I don't think I understand the situation, but I'll hazard a guess.

You can lookup a value in a table using a type of "query" called DLookup and then set the textbox.Value to the result.

txtBox1.Value = DLOOKUP.....

Here's what I have in my notebook about Dlookup:


The Dlookup function looks up one value in a desired table and therefore is a SELECT that returns a single value - you will therefore neeed to provide a WHERE clause geared to return a single value. The format is this (note you can lookup a value in a query-output, not just lookup a value in a table).

Dlookup(ColumnName, TableName or QueryName, WHERE clause)

In other words the WHERE clause must be set up as to return a single value from the column ColumnName. You can output the returned value in a message box by using the following message (just put the equals sign in front of the Dlookup function)

= Dlookup(ColumnName, TableName, WHERE clause)

Thus if the table exists, the following DLookup, if used in a msg box, would display "tblCustomers" in the msgbox (tables are Type 1 in the MSysObjects table)

= DLookUp("Name","MSysObjects", "Type = 1 AND Name = 'tblCustomers'")

Or, if you just want to check if the table exists, the following condition returns true or false, i.e. true if the table exists, false otherwise.
DLookUp("Name","MSysObjects", "Type = 1 AND Name = 'tblCustomers'") is Not Null

 

[jamiesuperman420]

Fantastic!! That's exactly what I was looking for.

Thank you very much!!