RE: please help
KARL DEWEY
SELECT [customer number], [customer name], [invoice amount], [paid]
FROM YourTable
WHERE [customer number] = "107" OR [customer number] = "122";
If your [customer number] field is a number datatype then this --
SELECT [customer number], [customer name], [invoice amount], [paid]
FROM YourTable
WHERE [customer number] = 107 OR [customer number] = 122;
--
Build a little, test a little.
"trre" wrote:
> i am trying to produce a query with the fields customer number, customer name, invoice amount, and paid for customers 107 and 122. what should the criteria be?
>
> ---
> frmsrcurl: http://msgroups.net/microsoft.public.access/
> .
>
MsWatts
if I don't have the forms!formname!controlname there but once I add that it
won't pull at all. Does anybody have a clue why its not pulling?
Armen Stein
<MsW...@discussions.microsoft.com> wrote:
>I am having trouble with my form pulling info from my query.
I think you mean "my query pulling info from my form."
>It will run fine
>if I don't have the forms!formname!controlname there but once I add that it
>won't pull at all. Does anybody have a clue why its not pulling?
You might post the SQL of your query so that we can take a look.
You might want to check the syntax and value of your Forms reference
by typing it into the immediate window (press Ctl-G). Begin with a
question mark, like this:
?Forms!Myformname!Mycontrolname
Is the form open? Does it return a value? Is the value what you
expected?
Then try replacing that reference with that hard-coded value just to
try it out in your query.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Jerry Whittle
Also if there are any spaces or other special characters, you might need
square brackets.
forms![formname]![controlname]
The form must be open for it to work. It can be invisible, but needs to be
open.
The control must have data in it.
There can be problems with data types. For examlpe with a date field in the
query, you may need something like this to convert the text to a date:
"#" & forms![formname]![controlname] & "#"
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
KARL DEWEY
run. The query is run when you open the form. Chicken and egg!
Add action to requery after update of text box.
--
Build a little, test a little.
KenSheridan via AccessMonster.com
May work for you Mercans, but not for us Yurpeans. If the value in the
control is in short date format it would change 4th July to 7th April! Best
way is to declare the parameter:
PARAMETERS
Forms![formname]![controlname] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate = Forms![formname]![controlname];
All the best for the holiday,
Ken Sheridan
Stafford, England
Jerry Whittle wrote:
>forms!formname!controlname must match the form and controlname exactly.
>Also if there are any spaces or other special characters, you might need
>square brackets.
>
> forms![formname]![controlname]
>
>The form must be open for it to work. It can be invisible, but needs to be
>open.
>
>The control must have data in it.
>
>There can be problems with data types. For examlpe with a date field in the
>query, you may need something like this to convert the text to a date:
>
>"#" & forms![formname]![controlname] & "#"
>> I am having trouble with my form pulling info from my query. It will run fine
>> if I don't have the forms!formname!controlname there but once I add that it
>> won't pull at all. Does anybody have a clue why its not pulling?
--
Message posted via http://www.accessmonster.com
KenSheridan via AccessMonster.com
entered in the form in a format such as 12/22/2009 might be misinterpreted as
an arithmetical expression, which in this case evaluates to 0.
000271505497986334. Now this happens to be the value stored by Access for a
date/time of 30 December 1899 00:00:23 (30 December 1899 being day-zero in
Access's date/time implementation). Its unlikely you'd have such a date in
the table, so no results.
Its less important to declare other parameter data types, but always prudent
to do so.
You can declare a parameter via the query design interface, selecting
Parameters from the Query menu in design view (or whatever the equivalent is
in Access 2007), or in SQL view, for which see my reply to Jerry.
Ken Sheridan
Stafford, England
--
MsWatts
was able to locate an error on one of my tables. I'm trying to fix that now.
I am unable to post my SQL because I am communicating via my Android phone
and I can't copy the text for some reason.
Also is there some type of code I can add to make the query ignore certain
fields when there is a null value?
Thanks,
Tammy Watts
"KenSheridan via AccessMonster.com" wrote:
> .
>
Jerry Whittle
You'd think that I'd remember after living near Oxford for 4 years.
Cheers!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"KenSheridan via AccessMonster.com" wrote:
> .
>
KenSheridan via AccessMonster.com
Putting:
Is Not Null
in the criteria row of a column in design view will mean that that the query
doesn't return that row if the column in question is Null. If you want to do
this for multiple columns then it needs to be on each column. If you don't
want the row returned where any of these are Null it has to be an OR
operation, which means putting the Is Not Null on separate lines in design
view. This could be tricky when combining these criteria with your parameter
reference to the form's control. If you don't want the row returned only
where all of these are Null it has to be an AND operation, which means
putting the Is Not Null on the same line in design view. That's less tricky
when combined with the parameter, but on the whole its easier to express this
sort of logic by writing it in SQL view. So we'd really need to see the SQL
and have a clear explanation of how you want the Nulls handled to give you
detailed advice.
Ken Sheridan
Stafford, England
MsWatts wrote:
>Thanks for all the wonderful suggestions. I used the immediate window and I
>was able to locate an error on one of my tables. I'm trying to fix that now.
>
>I am unable to post my SQL because I am communicating via my Android phone
>and I can't copy the text for some reason.
>
>Also is there some type of code I can add to make the query ignore certain
>fields when there is a null value?
>
>Thanks,
>Tammy Watts
>
>> Also try declaring the parameter, particularly if it’s a date. A date
>> entered in the form in a format such as 12/22/2009 might be misinterpreted as
>[quoted text clipped - 17 lines]
>> >if I don't have the forms!formname!controlname there but once I add that it
>> >won't pull at all. Does anybody have a clue why its not pulling?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
KenSheridan via AccessMonster.com
team there at the time. Wonderful city. I was there the same time a certain
William Jefferson Clinton was a Rhodes Scholar, but I don't recall our paths
ever crossing!
All the best for the holiday.
Ken Sheridan
Stafford, England
Jerry Whittle wrote:
>Great point.
>
>You'd think that I'd remember after living near Oxford for 4 years.
>
>Cheers!
>> Jerry:
>>
>[quoted text clipped - 31 lines]
KenSheridan via AccessMonster.com
team there at the time. Wonderful city. I was there the same time a certain
William Jefferson Clinton was a Rhodes Scholar, but I don't recall our paths
ever crossing!
All the best for the holiday.
Ken Sheridan
Stafford, England
Jerry Whittle wrote:
>Great point.
>
>You'd think that I'd remember after living near Oxford for 4 years.
>
>Cheers!
>> Jerry:
>>
>[quoted text clipped - 31 lines]
MsWatts
my form. However it isn't returning all the information due to the Null
values. Please see my code below. I am really having a tough time figuring
out what to do since I am fairly new to Access.
Option Compare Database
Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "Test 2", acViewNormal, acEdit
DoCmd.Close acForm, "Packaging Query Form"
End Sub
Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click
DoCmd.Close
Exit_Close_Form_Click:
Exit Sub
Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click
End Sub
Private Sub Run_Click()
On Error GoTo Err_Run_Click
Dim stDocName As String
stDocName = "Test 2"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Run_Click:
Exit Sub
Err_Run_Click:
MsgBox Err.Description
Resume Exit_Run_Click
End Sub
Thanks for all the help you have provided thus far,
Tammy Watts
"KenSheridan via AccessMonster.com" wrote:
> .
>
KenSheridan via AccessMonster.com
Are we talking about the "Test 2" query here?
I think we'll need more information about the query in terms of what table(s)
and columns it uses, how the tables are joined if there is more than one
table involved, and where these problematical Nulls are. The SQL would be a
big help, but if you can't give us that them try and describe the query as
completely as possible.
I'm wondering whether the Nulls arise from joining two or more tables where
there are not matching rows in every table. If so this is because the query
uses INNER JOINs, which is the default join type when you create a query in
design view unless you’ve specified otherwise in the relationship between two
tables. An INNER JOIN only returns rows where there are matches on both
sides. To include all rows from a table even if there is no match on one
side an OUTER JOIN is used. These can be LEFT or RIGHT OUTER JOINs depending
on which side the unmatched rows are.
In design view you can change the join type by selecting the join line
between the tables by clicking on it (it will thicken). Then right click and
select Properties. In the ensuing dialogue you have three choices of join
type. The interface for this may differ in Access 2007, which I don't have,
but should be broadly similar I'd guess.
Ken Sheridan
Stafford, England
>> Tammy:
>>
>[quoted text clipped - 36 lines]
eliene
"KARL DEWEY" <KARL...@discussions.microsoft.com> escreveu na mensagem
news:91D65779-6AF5-4700...@microsoft.com...