A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Search Forums
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Showing results 1 to 25 of 100
Search took 0.78 seconds.
Search: Posts made by: John Spencer
Forum: Setting Up & Running Reports June 7th, 2010, 02:30 PM Posted to microsoft.public.access.reports
Replies: 5
Views: 2,000
Posted By John Spencer
Report made of a query cross table

You can copy the query, modify it, and use the modified copy for this one report.

Also, if you are using VBA code to call the report, you should be able to
include the filter

Dim strFilter as...
Forum: Running & Setting Up Queries June 7th, 2010, 02:24 PM Posted to microsoft.public.access.queries
Replies: 5
Views: 2,091
Posted By John Spencer
Delete Query not Working

You can write that as
DELETE [NumberDialed]
FROM [DailyCalls]
WHERE [DailyCalls].[NumberDialed] IN
(SELECT [PersonalCalls].[PhoneNumber]
FROM [PersonalCalls]
WHERE...
Forum: Running & Setting Up Queries June 7th, 2010, 01:59 PM Posted to microsoft.public.access.queries
Replies: 3
Views: 1,741
Posted By John Spencer
Use of Parntheses in Expressions

Breaking that all down and formatting it for readability, I think you could
use the following expression.
Sum(
IIf(
[Mapping]![Pricing]="CPM",
[TPImpressions]/1000*[Mapping]![Price],
...
Forum: Setting Up & Running Reports June 6th, 2010, 09:16 PM Posted to microsoft.public.access.reports
Replies: 5
Views: 2,000
Posted By John Spencer
Report made of a query cross table

TRANSFORM Sum([Qry Base].CUSTO) AS SomaDeCUSTO
SELECT [Qry Base].TELEFONE, [Qry Base].CONTA, [Qry Base].[Dt Adesão]
, [Qry Base].Pacote, [Qry Base].Status, [Qry Base].Protocolo, [Qry Base].Rede
,...
Forum: Running & Setting Up Queries June 6th, 2010, 09:07 PM Posted to microsoft.public.access.queries
Replies: 4
Views: 686
Posted By John Spencer
JOIN Query very slow

Assuming that SerialAndDate is a combination of the fields Serial and Date, I
would use a query like the following and ignore the SerialAndDate field.

SELECT Table1.Serial, Table1.Item,...
Forum: Running & Setting Up Queries June 6th, 2010, 08:58 PM Posted to microsoft.public.access.queries
Replies: 5
Views: 2,091
Posted By John Spencer
Delete Query not Working

My error. Exteraneous AND

DELETE
FROM [DailyCalls]
WHERE [DailyCalls].[NumberDialed] IN
(SELECT [PersonalCalls].[PhoneNumber]
FROM [PersonalCalls]
WHERE [PersonalCalls].[PhoneNumber] is...
Forum: Running & Setting Up Queries June 6th, 2010, 08:44 PM Posted to microsoft.public.access.queries
Replies: 3
Views: 383
Posted By John Spencer
Optimization of Between Clause Queries

You really need to have a separate index on each of the three fields to get
performance.

Your index could get used for the r field, but it would be useless for the
other two fields.

John...
Forum: Setting Up & Running Reports June 3rd, 2010, 09:30 PM Posted to microsoft.public.access.reports
Replies: 10
Views: 2,697
Posted By John Spencer
Firstname plus Middlename??

Steve,
You have made an error in your expression.

" " & MiddleName will always return at least a space.
The plus sign will see the space and return it.

If you wanted to be safe you could...
Forum: Running & Setting Up Queries June 3rd, 2010, 09:24 PM Posted to microsoft.public.access.queries
Replies: 6
Views: 405
Posted By John Spencer
Storing time as integers

You can do it with some math.

If you store the duration in seconds. And for example the duration is 7356

There is no field type in Access that stores duration.

One way to handle duration is to...
Forum: Running & Setting Up Queries June 3rd, 2010, 08:36 PM Posted to microsoft.public.access.queries
Replies: 7
Views: 350
Posted By John Spencer
querying unrelated tables

Might help if I included the table name
Parameters [Period Start] as DateTime, [Period End] as DateTime;
SELECT EmpID, [Date], [Time-In], [Time-out], Null as [Paid Holiday]
FROM...
Forum: Setting Up & Running Reports June 3rd, 2010, 07:10 PM Posted to microsoft.public.access.reports
Replies: 10
Views: 2,697
Posted By John Spencer
Firstname plus Middlename??

You should be using the & concatenate operator instead of the + operator.

The plus returns blank if any of the values are null (blank/never entered).

The & operator treats nulls as if they were a...
Forum: Setting Up & Running Reports June 3rd, 2010, 07:06 PM Posted to microsoft.public.access.reports
Replies: 3
Views: 393
Posted By John Spencer
Report formatting hangs

Usually, the amount of the data in the section is TOO large to keep together.
So Access looks for another page to put it on, but if the amount of data is
too large to fit on one page Access looks...
Forum: Running & Setting Up Queries June 3rd, 2010, 07:01 PM Posted to microsoft.public.access.queries
Replies: 7
Views: 350
Posted By John Spencer
querying unrelated tables

Best I can think of right now is a Union query

Parameters [Period Start] as DateTime, [Period End] as DateTime;
SELECT EmpID, [Date], [Time-In], [Time-out], Null as [Paid Holiday]
FROM
WHERE [Date]...
Forum: Running & Setting Up Queries June 3rd, 2010, 06:49 PM Posted to microsoft.public.access.queries
Replies: 2
Views: 367
Posted By John Spencer
Default Dates

No, there is no way to have a default date DISPLAYED. You can however have a
default date For instance to default to a range of Jan 1 of the current year
to Dec 31 of the current year.


Between...
Forum: Setting Up & Running Reports June 3rd, 2010, 01:31 PM Posted to microsoft.public.access.reports
Replies: 2
Views: 1,975
Posted By John Spencer
Merge a field data for all records in a table into one output

You need to concatenate one field from a group of records.

Here are links (url) to three examples using VBA.

Duane Hookom
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Allen...
Forum: Setting Up & Running Reports June 3rd, 2010, 01:29 PM Posted to microsoft.public.access.reports
Replies: 3
Views: 383
Posted By John Spencer
Printing a report 2-up

Open your report in design view
In Access 2003 and earlier
SELECT File: Page Setup from the menu
Click on the Columns tab
Change Number of Columns to 2
Change Column Size Width to a value that is...
Forum: Running & Setting Up Queries June 3rd, 2010, 01:21 PM Posted to microsoft.public.access.queries
Replies: 2
Views: 442
Posted By John Spencer
Changing constants in a query to user input values

If I understand correctly, you want something like

Between DateAdd("h",17,[Forms]![FormName]![StartDate]) and
DateAdd("h",5,[Forms]![FormName]![EndDate])

Since that would only work for a one day...
Forum: New Users June 3rd, 2010, 01:02 PM Posted to microsoft.public.access.gettingstarted
Replies: 2
Views: 1,660
Posted By John Spencer
Selecting the last 10 records from a specific date

A query that would look something like the following.

SELECT TOP 10 NameField, DateField
FROM SomeTable
WHERE DateField = Date()
ORDER BY DateField DESC

In query design view
== Add the table
==...
Forum: General Discussion June 2nd, 2010, 09:26 PM Posted to microsoft.public.access
Replies: 2
Views: 386
Posted By John Spencer
make dialog box bigger

Guess what? There is no way to resize the dialog box.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Steve wrote:
When I'm designing ...
Forum: Running & Setting Up Queries June 2nd, 2010, 06:24 PM Posted to microsoft.public.access.queries
Replies: 5
Views: 2,091
Posted By John Spencer
Delete Query not Working

It should be

DELETE DistinctRow [DailyCalls].NumberDialed
FROM DailyCalls
WHERE (((DailyCalls.NumberDialed)=[PersonalCalls].[PhoneNumber]));

However that will fail since you do not a have a...
Forum: General Discussion June 2nd, 2010, 06:17 PM Posted to microsoft.public.access
Replies: 4
Views: 388
Posted By John Spencer
having one field display when another field changes

Well you could build a complex calculated field in a query.

Switch(ChStat=10,"Printed",ChStat=20,"Located",ChStat=30,"Destroyed")


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop...
Forum: Running & Setting Up Queries June 2nd, 2010, 04:17 PM Posted to microsoft.public.access.queries
Replies: 2
Views: 379
Posted By John Spencer
SQL Update question

No, it can be done in one query. The error message indicates that one of the
field names is incorrectly spelled or that True is being misunderstood for
some reason.

Try
strSQL = "UPDATE tblconfig...
Forum: General Discussion June 2nd, 2010, 01:59 PM Posted to microsoft.public.access
Replies: 4
Views: 388
Posted By John Spencer
having one field display when another field changes

In tables and fields you should have a table that has a field that has the
CHSTAT values and a field that has the equivalent text values.

Then you join that table to the current table in a query on...
Forum: Setting Up & Running Reports June 2nd, 2010, 01:48 PM Posted to microsoft.public.access.reports
Replies: 5
Views: 2,000
Posted By John Spencer
Report made of a query cross table

Post the SQL of your cross tab query.

It is possible that all you have to do is add a having clause to it.
Generically that might look something like:

TRANSFORM Sum(SomeValue) as MonthlySum
SELECT...
Forum: Running & Setting Up Queries June 2nd, 2010, 01:40 PM Posted to microsoft.public.access.queries
Replies: 7
Views: 428
Posted By John Spencer
Union Query and Field Alias

In the report use the control's format property. (Simplest solution in my mind)

You can format the calculated field using the Format function in the query
HOWEVER you would need to list all the...
Showing results 1 to 25 of 100

 
Forum Jump

All times are GMT +1. The time now is 06:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright ©2004-2018 OfficeFrustration.
The comments are property of their posters.