PLEASE HELP! - DATA ACCESS PAGE

[Guest]

Hi
I have created a data access page for my database and have tested it by
closing down the database and filling in the data access page. This works
fine and when I go back into the database it has created a record, the only
problem is when I run reports & queries etc it will not display any record
created via the data access page? Although I can see it if I open the form or
table, it won't show them in reports etc. Any ideas?
Thanks

 

[Dirk Goldgar]

Hi
I have created a data access page for my database and have tested it
by closing down the database and filling in the data access page.
This works fine and when I go back into the database it has created a
record, the only problem is when I run reports & queries etc it will
not display any record created via the data access page? Although I
can see it if I open the form or table, it won't show them in reports
etc. Any ideas?
Thanks

Could it be that your reports and queries are applying criteria that the
records entered via the DAP don't meet? Pick one of the reports and
examine its recordsource query, looking for criteria applied to fields
in the database that maybe your DAP is not filling in.

 

[Guest]

Hi Dirk

Thanks for getting back to me. I have checked the query, the table and the
data access page and they all are pointing to the right place, the fields is
just a text fields with no other specific criteria.

When I look in the table, the customers name is there, but when I run the
query based on thier name it doesn't find anything, I have checked that it is
being spelt correctly etc and that is all ok, I just can't see what the
problem is..... any other ideas?

Tracy

 

[Dirk Goldgar]

Hi Dirk

Thanks for getting back to me. I have checked the query, the table
and the data access page and they all are pointing to the right
place, the fields is just a text fields with no other specific
criteria.

When I look in the table, the customers name is there, but when I run
the query based on thier name it doesn't find anything, I have
checked that it is being spelt correctly etc and that is all ok, I
just can't see what the problem is..... any other ideas?

What is the exact SQL of the query? If you build and execute it in
code, what is the code?

 

[Guest]

Hi Dirk

I built it using the query in design, I am not a programmer. This is the sql
code it has created:

SELECT [Main Project Table].[Customer Name], [Main Project Table].[Project
ID], [Main Project Table].[Project/Job description], [Main Project
Table].[Date Arrived], [Main Project Table].[Project Name], [Main Project
Table].[Project Manager], [Resource Scheduler].Status, [Resource
Scheduler].[Status Notes], [Resource Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main Project
Table].[Project ID] = [Resource Scheduler].[Project ID]
WHERE ((([Main Project Table].[Customer Name]) Like [*]));

Thanks
Tracy

 

[Dirk Goldgar]

Hi Dirk

I built it using the query in design, I am not a programmer. This is
the sql code it has created:

SELECT [Main Project Table].[Customer Name], [Main Project
Table].[Project ID], [Main Project Table].[Project/Job description],
[Main Project Table].[Date Arrived], [Main Project Table].[Project
Name], [Main Project Table].[Project Manager], [Resource
Scheduler].Status, [Resource Scheduler].[Status Notes], [Resource
Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main
Project Table].[Project ID] = [Resource Scheduler].[Project ID]
WHERE ((([Main Project Table].[Customer Name]) Like [*]));

The WHERE clause is wrong. Corrected for syntax, it would be:

WHERE ((([Main Project Table].[Customer Name]) Like "*"));

(and it has unnecessary extra parentheses that the Access query designer
likes to add, but they are not the problem).

However, there's no real point in saying ' Like "*" ', because that just
matches everything, in which case you don't need the WHERE clause at
all. Try just this:

SELECT
[Main Project Table].[Customer Name],
[Main Project Table].[Project ID],
[Main Project Table].[Project/Job description],
[Main Project Table].[Date Arrived],
[Main Project Table].[Project Name],
[Main Project Table].[Project Manager],
[Resource Scheduler].Status,
[Resource Scheduler].[Status Notes],
[Resource Scheduler].[Colleague Name]
FROM
[Main Project Table]
INNER JOIN
[Resource Scheduler]
ON
[Main Project Table].[Project ID] =
[Resource Scheduler].[Project ID];

Your use of spaces in your table and field names forces all those names
to be surrounded with brackets, as you see. In future designs, you'll
probably find it easier to avoid using spaces and non-alphanumeric
characters (like "/") in table and field names.

 

[Guest]

Thanks Dirk I will try it, the reason I had the 'Like [*]' was because I want
users to type in the name of the customer and the query then only shows those
records, which is what the problem is, records put on via the data access
page will not show.

Hi Dirk

I built it using the query in design, I am not a programmer. This is
the sql code it has created:

SELECT [Main Project Table].[Customer Name], [Main Project
Table].[Project ID], [Main Project Table].[Project/Job description],
[Main Project Table].[Date Arrived], [Main Project Table].[Project
Name], [Main Project Table].[Project Manager], [Resource
Scheduler].Status, [Resource Scheduler].[Status Notes], [Resource
Scheduler].[Colleague Name]
FROM [Main Project Table] INNER JOIN [Resource Scheduler] ON [Main
Project Table].[Project ID] = [Resource Scheduler].[Project ID]
WHERE ((([Main Project Table].[Customer Name]) Like [*]));

The WHERE clause is wrong. Corrected for syntax, it would be:

WHERE ((([Main Project Table].[Customer Name]) Like "*"));

(and it has unnecessary extra parentheses that the Access query designer
likes to add, but they are not the problem).

However, there's no real point in saying ' Like "*" ', because that just
matches everything, in which case you don't need the WHERE clause at
all. Try just this:

SELECT
[Main Project Table].[Customer Name],
[Main Project Table].[Project ID],
[Main Project Table].[Project/Job description],
[Main Project Table].[Date Arrived],
[Main Project Table].[Project Name],
[Main Project Table].[Project Manager],
[Resource Scheduler].Status,
[Resource Scheduler].[Status Notes],
[Resource Scheduler].[Colleague Name]
FROM
[Main Project Table]
INNER JOIN
[Resource Scheduler]
ON
[Main Project Table].[Project ID] =
[Resource Scheduler].[Project ID];

Your use of spaces in your table and field names forces all those names
to be surrounded with brackets, as you see. In future designs, you'll
probably find it easier to avoid using spaces and non-alphanumeric
characters (like "/") in table and field names.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks Dirk I will try it, the reason I had the 'Like [*]' was
because I want users to type in the name of the customer and the
query then only shows those records, which is what the problem is,
records put on via the data access page will not show.

Then try this:

WHERE [Main Project Table].[Customer Name]
Like "*" & [Enter customer name, or some part thereof] & "*";

That will get all customers whose names contain the user's entry.

Or try this:

WHERE [Main Project Table].[Customer Name]
Like [Enter customer name, or the first part of it] & "*";

That will get all customers whose names start with the user's entry.

Or try this:

WHERE
([Main Project Table].[Customer Name] =
[Enter customer name])
OR
([Enter customer name] Is Null)

That should get just the exact customer whose name was entered, but
bring up all customers if no customer name is entered.

 

[Guest]

Thanks for your help Dirk, I will see which one we prefer.
Regards
Tracy

Thanks Dirk I will try it, the reason I had the 'Like [*]' was
because I want users to type in the name of the customer and the
query then only shows those records, which is what the problem is,
records put on via the data access page will not show.

Then try this:

WHERE [Main Project Table].[Customer Name]
Like "*" & [Enter customer name, or some part thereof] & "*";

That will get all customers whose names contain the user's entry.

Or try this:

WHERE [Main Project Table].[Customer Name]
Like [Enter customer name, or the first part of it] & "*";

That will get all customers whose names start with the user's entry.

Or try this:

WHERE
([Main Project Table].[Customer Name] =
[Enter customer name])
OR
([Enter customer name] Is Null)

That should get just the exact customer whose name was entered, but
bring up all customers if no customer name is entered.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)