Query can't find field on form???

[Lost500]

Hi everybody,
so i have a query that gets it's criterea from the form that the report button is on. in the query criterea i have

[Forms]![frmLeaseInfoReadOnly]![LeaseNumber]

this has always worked just fine for me but today when i walked in and ran the report it prompted me for the parameter value instead of getting it from the form itself. nothing has been changed and there is a field on frmLeaseInfoReadOnly called LeaseNumber so what gives? why and how could it work yesterday and not today and whats wrong with the criterea?

Please help i don't know whats going on do i have a virus or did something else happen that could throw everything off?

 

[lameid]

If you type the following in the immediate window does it return what you expect?

? [Forms]![frmLeaseInfoReadOnly]![LeaseNumber]

Are you sure your value is commited in the form? ...save the record or tab out of the control.

Is the datatype of the field text? Remove any paramter reference. Is it not text? Add a parameter reference (Query Menu, Parameters)

 

[traingamer]

And try repairing the database, as well (after backup). It may have become corrupted since yesterday.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Lost500]

thanks for the input and to answer your suggestions,

1.) yes i if i type the code you listed in the immediate window it returns the correct value.

2.) yes the value is commited these are values that have been in the table for awhile so i don't think that's it.

3.) this is a text value and I do not have any parameter references (but there are other field types in the query do i need to list parameters for them if so what are they i have number, date/time and yes/no fields)

4.) i did try repairing the database and to no avail

I can make a simular report open but it shows #Name? where i used two fields in an expression in a text box as follows:

=[UnitName] & " " & [UnitGrossAcres] & " ac."

both fields are included in the query and if i assign them to seperate text boxes the correct value appears?

Please help i'm FUBAR'd if this database worked the day before yesterday and now its just broken. could this be due to changes being made to the table structure on the sql database that these tables are linked to? i've refreshed and relinked all of my tables since the changes though.

 

[lameid]

For field datatypes other than text, if the the field appears in the where or having clause and a parameter is used for criteria, you should specify it in the query.

If you relinked all the tables Access should be aware of any changes. Worse case you get prompted for a value becaue access thinks the field is a parameter.


#Name... If this is in your query let me know. But on the report side, it likely indicates a syntax issue like a circular reference... For example you might have a field named field1. If you have a control that has a name of field1 but a control source that is not field1 you will likely see this error.

 

[Lost500]

Thanks Lameid,
the only field on the form that is used as a criteria in the query is a text field. or atleast thats what access calls it... on the sql database its a nvarchar(50) and really i don't know why its that on sql if there is something better let me know

what do you mean by access might think the field is a parameter? and what can be done about that?

#Name is on the report side.. i thought the code that i listed earlier looked at the fields in the query which match the names in the []'s is that not the case?

'If you have a control that has a name of field1 but a control source that is not field1 you will likely see this error.'

do you mean if i had a field in my query named UnitName the text box on the report also needs to be named UnitName? or does it need to be something else like txtUnitName?

Thanks for the help!

 

[lameid]

Ok:

Control Name: Unit Name
Control Source: Unit Name

Ok:
Contorl Name: txtUnitName
Control Source: Unit Name

Not Ok:
Control Name: Unit Name
Control Source: =[Unit Code] & " " & [Unit Name]

Not Ok, if Unit Name is a field:
Control Name: Unit Name
Control Source: Unit Code


Not Ok because Spaces and some other characters require square brackets around the problem field names, exception being if it is only one field without the =:

Control Name: txtUnitName
Control Source: =Unit Code & " " & Unit Name

Syntaxtically speaking an Access report does not know the difference between a control or a field. Therefore, there can not be any ambiguous references or things break.


Access may think a field is a parameter if you have a SQL statement with a field that is no longer valid.

Perhaps your query is...

Select Table1.[Unit Name]
From Table1

If someone changes the field name to Unit_Name, the SQL will not change in the case of a linked database. In the case of a native access database it will change it if autocorrect is on (Access 97 and earlier did not have this 'feature') which you should turn off anyways for performance reasons. When Access does not find a value for [Unit Name], it will prompt you for it.

 

[Lost500]

so i'm getting confused with what parts we are talking about and i'm guessing the report is too ('Syntaxtically speaking an Access report does not know the difference between a control or a field. Therefore, there can not be any ambiguous references or things break.
' control on a form and a field from the query??) so here is what i have

a table with fields named UnitName and UnitGrossAcres.

I created a query that includes said fields so my query has fields named UnitName and UnitGrossAcres.

niether field has a space in it's name. let me know if you were talking about the space i try to insert with " " because i do have one of those and i didn't think it was a problem.

on my report i have a text box named txtUnitName it's control source is

=[UnitName] & " " & [UnitGrossAcres] & "ac."

in an attempt to have a 400 acre unit named bob to be presented as 'bob 400 ac.' in the text box named txtUnitName.

am i breaking any of the rules you have mentioned? the control source is a query that calls two other queries is that bad? i thought i could do that and it's always worked before.

Thanks for all the help i feel like we're getting somewhere!

 

[lameid]

No, the control name (txtUnitName) is not a field so you are ok.

Your second report, does it run without asking for the parameter while the first one still does?

Are you absolutely sure you are using field names (no typo's)?

Is your access fully patched?

 

[Lost500]

how do i check that my access is fully patched?

there are no typos i'm sure of it.

and yes my second report doesn't ask for teh parameter and the first one still does

in a new developement the #Name? only shows part of the time and i can sometimes go to design view and then back to report view and it picks the fields up and returns the desired value.

I really don't know what is causing this to happen, do i need to rebuild? will it just happen to the next access front end at some point?

 

[lameid]

If you are running XP or before go to
Update.microsoft.com and look for updates and be sure to install Microsoft Update if you have not before.

Go back to the site and keep running updates until there are no critical updates left. That should patch both windows and Office as well as other MS apps.

I believe Vista has this already integrated so all you have to do is launch the updates... somewhere under the
"start" button, I don't have it commited to memory yet.


If it happens some of the time and not others, I have to wonder if it is data as opposed to syntax. You could try various filters to narrow down and identify the problem record(s). Hoepfully looking at the example, the problem will jump out at you.

A possiblity is corruption. Sometimes importing everything into fresh database file(s) fixes corruption issues where a compact and repair fails.

 

[Lost500]

Thanks lameid,

other than trying various filters to try and find problem data is there anything i can use to anyalize the data for corruptions or other types of bad data? thanks for all the help i hate that this is a problem that can't be nailed down as to finding exactly what is wrong. Thanks again for the help!

 

[lameid]

You could look in the table directly. I remember someone suggesting to put the cursor in the first row and column and hold it down until it hits an error. You might also see #Error in the data. On the other hand there may not be a real problem in the data just in the way it evaluates.

If it is data, the same filter will cause the same error to occur every time. Once you have the error, change the filter so you are looking at a smaller and smaller set of data. I think this is the best option unless there is something obvious wrong with the control on the report... If you post the control source and the datatypes of all the fields used, we might see something obvious.