field Null but not Null

Don't really know how to explain this, but here goes.  Have a table
with fields [BLOCK], [NUMBER], and [ALPHA].  [ALPHA] can be null.
These are concantenated together to form Acct# (block & "-" & number &
alpha).   Example, 9-152 is different account from 9-152G.

I append block billing charges to a temp table for review, and then
post (append) those charges to transaction table.  But, here is
strange thing. When I run a totals query, sometimes there are two
records instead of one.

For example; acct# 9-152 might show as:
Acct     SumOfTransactions
9-152     $20.00
9-152     $15.00

instead of just:
9-152     $35.00

Access sees "something" in the Alpha field sometimes.  In example
above, if I bring up those two records, and put cursour in the Alpha
field and backspace, or tab into field and delete, then Access will
still see them as two records.  But, if I put something in the Alpha
field, save record, go back and delete out what I just put in, then
Access now considers them the same and will give me just one total.

If I run a query using Is Null in Alpha field, then Access returns
both records, so I don't understand why Access then returns two
records in a totals query.  (leaving Acct# out of the query still
returns 2 records, btw)

It only happens sometimes.  For example, out of approx 800 records I
just appended, there were 32 of these, where the totals query came up
with two different records rather than one.

I'm stumped.........Anybody ever hear of anything like this?

by the way, the block, number, alpha are used for sorting and
differenct charge levels. Since the Alpha field can be blank, I didn't
use this as a composite PK, but rather the Acct# is the PK.

Thanks for any help.
John
0
johnboy7676
4/7/2010 3:49:17 PM
access 16762 articles. 3 followers. Follow

10 Replies
1067 Views

Similar Articles

[PageSpeed] 15

Access 2003, by the way.............


On Wed, 07 Apr 2010 09:49:17 -0600, johnboy7676@yahoo.com wrote:

>Don't really know how to explain this, but here goes.  Have a table
>with fields [BLOCK], [NUMBER], and [ALPHA].  [ALPHA] can be null.
>These are concantenated together to form Acct# (block & "-" & number &
>alpha).   Example, 9-152 is different account from 9-152G.
>
>I append block billing charges to a temp table for review, and then
>post (append) those charges to transaction table.  But, here is
>strange thing. When I run a totals query, sometimes there are two
>records instead of one.
>
>For example; acct# 9-152 might show as:
>Acct     SumOfTransactions
>9-152     $20.00
>9-152     $15.00
>
>instead of just:
>9-152     $35.00
>
>Access sees "something" in the Alpha field sometimes.  In example
>above, if I bring up those two records, and put cursour in the Alpha
>field and backspace, or tab into field and delete, then Access will
>still see them as two records.  But, if I put something in the Alpha
>field, save record, go back and delete out what I just put in, then
>Access now considers them the same and will give me just one total.
>
>If I run a query using Is Null in Alpha field, then Access returns
>both records, so I don't understand why Access then returns two
>records in a totals query.  (leaving Acct# out of the query still
>returns 2 records, btw)
>
>It only happens sometimes.  For example, out of approx 800 records I
>just appended, there were 32 of these, where the totals query came up
>with two different records rather than one.
>
>I'm stumped.........Anybody ever hear of anything like this?
>
>by the way, the block, number, alpha are used for sorting and
>differenct charge levels. Since the Alpha field can be blank, I didn't
>use this as a composite PK, but rather the Acct# is the PK.
>
>Thanks for any help.
>John
0
johnboy7676
4/7/2010 3:53:10 PM
Hi - 

Untested, but try concatenating a blank or zero-length string instead of a
null when Alpha is Null.  Use the Nz function, like this:

Acct# : block & "-" & number & nz(alpha," ")

John


johnboy7676@yahoo.com wrote:
>Don't really know how to explain this, but here goes.  Have a table
>with fields [BLOCK], [NUMBER], and [ALPHA].  [ALPHA] can be null.
>These are concantenated together to form Acct# (block & "-" & number &
>alpha).   Example, 9-152 is different account from 9-152G.
>
>I append block billing charges to a temp table for review, and then
>post (append) those charges to transaction table.  But, here is
>strange thing. When I run a totals query, sometimes there are two
>records instead of one.
>
>For example; acct# 9-152 might show as:
>Acct     SumOfTransactions
>9-152     $20.00
>9-152     $15.00
>
>instead of just:
>9-152     $35.00
>
>Access sees "something" in the Alpha field sometimes.  In example
>above, if I bring up those two records, and put cursour in the Alpha
>field and backspace, or tab into field and delete, then Access will
>still see them as two records.  But, if I put something in the Alpha
>field, save record, go back and delete out what I just put in, then
>Access now considers them the same and will give me just one total.
>
>If I run a query using Is Null in Alpha field, then Access returns
>both records, so I don't understand why Access then returns two
>records in a totals query.  (leaving Acct# out of the query still
>returns 2 records, btw)
>
>It only happens sometimes.  For example, out of approx 800 records I
>just appended, there were 32 of these, where the totals query came up
>with two different records rather than one.
>
>I'm stumped.........Anybody ever hear of anything like this?
>
>by the way, the block, number, alpha are used for sorting and
>differenct charge levels. Since the Alpha field can be blank, I didn't
>use this as a composite PK, but rather the Acct# is the PK.
>
>Thanks for any help.
>John

-- 
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via http://www.accessmonster.com

0
J_Goddard
4/7/2010 4:11:48 PM
Thanks, but the Fields are "defined" in a master acct table, such that
there are 4 fields (among others); BLOCK, NUMBER,ALPHA, ACCTNUM
so that the concatenation occurs when Acct# is first created.  Later,
when fillinjg the temp table with billing charges, it is appending
those Fields, and since ALPHA is Null in these cases, whatever ACCTNUM
is doesn't really matter...........I think. 

Maybe I should try as you say, or get rid of ACCTNUM, make ALPHA
required field, put in empty string, or zero and then in report don't
display zero.   Make composite PK.   I just thought it very strange.
John


On Wed, 07 Apr 2010 16:11:48 GMT, "J_Goddard via AccessMonster.com"
<u37558@uwe> wrote:

>Hi - 
>
>Untested, but try concatenating a blank or zero-length string instead of a
>null when Alpha is Null.  Use the Nz function, like this:
>
>Acct# : block & "-" & number & nz(alpha," ")
>
>John
>
>
>johnboy7676@yahoo.com wrote:
>>Don't really know how to explain this, but here goes.  Have a table
>>with fields [BLOCK], [NUMBER], and [ALPHA].  [ALPHA] can be null.
>>These are concantenated together to form Acct# (block & "-" & number &
>>alpha).   Example, 9-152 is different account from 9-152G.
>>
>>I append block billing charges to a temp table for review, and then
>>post (append) those charges to transaction table.  But, here is
>>strange thing. When I run a totals query, sometimes there are two
>>records instead of one.
>>
>>For example; acct# 9-152 might show as:
>>Acct     SumOfTransactions
>>9-152     $20.00
>>9-152     $15.00
>>
>>instead of just:
>>9-152     $35.00
>>
>>Access sees "something" in the Alpha field sometimes.  In example
>>above, if I bring up those two records, and put cursour in the Alpha
>>field and backspace, or tab into field and delete, then Access will
>>still see them as two records.  But, if I put something in the Alpha
>>field, save record, go back and delete out what I just put in, then
>>Access now considers them the same and will give me just one total.
>>
>>If I run a query using Is Null in Alpha field, then Access returns
>>both records, so I don't understand why Access then returns two
>>records in a totals query.  (leaving Acct# out of the query still
>>returns 2 records, btw)
>>
>>It only happens sometimes.  For example, out of approx 800 records I
>>just appended, there were 32 of these, where the totals query came up
>>with two different records rather than one.
>>
>>I'm stumped.........Anybody ever hear of anything like this?
>>
>>by the way, the block, number, alpha are used for sorting and
>>differenct charge levels. Since the Alpha field can be blank, I didn't
>>use this as a composite PK, but rather the Acct# is the PK.
>>
>>Thanks for any help.
>>John
0
johnboy7676
4/7/2010 4:21:37 PM
On Wed, 07 Apr 2010 09:49:17 -0600, johnboy7676@yahoo.com wrote:

>Don't really know how to explain this, but here goes.  Have a table
>with fields [BLOCK], [NUMBER], and [ALPHA].  [ALPHA] can be null.
>These are concantenated together to form Acct# (block & "-" & number &
>alpha).   Example, 9-152 is different account from 9-152G.
>
>I append block billing charges to a temp table for review, and then
>post (append) those charges to transaction table.  But, here is
>strange thing. When I run a totals query, sometimes there are two
>records instead of one.
>
>For example; acct# 9-152 might show as:
>Acct     SumOfTransactions
>9-152     $20.00
>9-152     $15.00
>
>instead of just:
>9-152     $35.00
>
>Access sees "something" in the Alpha field sometimes.  In example
>above, if I bring up those two records, and put cursour in the Alpha
>field and backspace, or tab into field and delete, then Access will
>still see them as two records.  But, if I put something in the Alpha
>field, save record, go back and delete out what I just put in, then
>Access now considers them the same and will give me just one total.

Check the properties of the Alpha field. Is its "Allow Zero Length" property
set to Yes? If so, it may contain either a NULL - undefined, no contents - or
a zero length string - a precisely defined string value "". They'll be seen as
different!

It's also possible that you have some other nonprinting character. Try
including a calculated field Len([ALPHA])  in a query to see if there is
something other than null in the field. If there is you can use the Asc()
function to get its ASCII value.
-- 

             John W. Vinson [MVP]
0
John
4/7/2010 5:02:16 PM
Where and how is the acctnum created?  This may be the source of your issue.  
I would check the code that creates the ACCTNUM and fix it.  That will 
prevent future issues from coming up.

Back up your database, then clean up the existing data.

You should clean up your ACCTNUM data by trimming off any extra spaces at 
the end of the string.  It could be that ALPHA is null, but your ACCTNUM is 
not.  This would need to be cleaned up in both the master acct table and in 
the billing charges table (and anywhere else the ACCTNUM is used).  

Alternatively, do not use ACCTNUM from the tables at all, but 'build' that 
value each time, and group on the built value.

-- 
Daryl S


"johnboy7676@yahoo.com" wrote:

> 
> Thanks, but the Fields are "defined" in a master acct table, such that
> there are 4 fields (among others); BLOCK, NUMBER,ALPHA, ACCTNUM
> so that the concatenation occurs when Acct# is first created.  Later,
> when fillinjg the temp table with billing charges, it is appending
> those Fields, and since ALPHA is Null in these cases, whatever ACCTNUM
> is doesn't really matter...........I think. 
> 
> Maybe I should try as you say, or get rid of ACCTNUM, make ALPHA
> required field, put in empty string, or zero and then in report don't
> display zero.   Make composite PK.   I just thought it very strange.
> John
> 
> 
> On Wed, 07 Apr 2010 16:11:48 GMT, "J_Goddard via AccessMonster.com"
> <u37558@uwe> wrote:
> 
> >Hi - 
> >
> >Untested, but try concatenating a blank or zero-length string instead of a
> >null when Alpha is Null.  Use the Nz function, like this:
> >
> >Acct# : block & "-" & number & nz(alpha," ")
> >
> >John
> >
> >
> >johnboy7676@yahoo.com wrote:
> >>Don't really know how to explain this, but here goes.  Have a table
> >>with fields [BLOCK], [NUMBER], and [ALPHA].  [ALPHA] can be null.
> >>These are concantenated together to form Acct# (block & "-" & number &
> >>alpha).   Example, 9-152 is different account from 9-152G.
> >>
> >>I append block billing charges to a temp table for review, and then
> >>post (append) those charges to transaction table.  But, here is
> >>strange thing. When I run a totals query, sometimes there are two
> >>records instead of one.
> >>
> >>For example; acct# 9-152 might show as:
> >>Acct     SumOfTransactions
> >>9-152     $20.00
> >>9-152     $15.00
> >>
> >>instead of just:
> >>9-152     $35.00
> >>
> >>Access sees "something" in the Alpha field sometimes.  In example
> >>above, if I bring up those two records, and put cursour in the Alpha
> >>field and backspace, or tab into field and delete, then Access will
> >>still see them as two records.  But, if I put something in the Alpha
> >>field, save record, go back and delete out what I just put in, then
> >>Access now considers them the same and will give me just one total.
> >>
> >>If I run a query using Is Null in Alpha field, then Access returns
> >>both records, so I don't understand why Access then returns two
> >>records in a totals query.  (leaving Acct# out of the query still
> >>returns 2 records, btw)
> >>
> >>It only happens sometimes.  For example, out of approx 800 records I
> >>just appended, there were 32 of these, where the totals query came up
> >>with two different records rather than one.
> >>
> >>I'm stumped.........Anybody ever hear of anything like this?
> >>
> >>by the way, the block, number, alpha are used for sorting and
> >>differenct charge levels. Since the Alpha field can be blank, I didn't
> >>use this as a composite PK, but rather the Acct# is the PK.
> >>
> >>Thanks for any help.
> >>John
> .
> 
0
Utf
4/7/2010 5:17:02 PM
On Wed, 07 Apr 2010 11:02:16 -0600, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Wed, 07 Apr 2010 09:49:17 -0600, johnboy7676@yahoo.com wrote:
>
>>Don't really know how to explain this, but here goes.  Have a table
>>with fields [BLOCK], [NUMBER], and [ALPHA].  [ALPHA] can be null.
>>These are concantenated together to form Acct# (block & "-" & number &
>>alpha).   Example, 9-152 is different account from 9-152G.
>>
>>I append block billing charges to a temp table for review, and then
>>post (append) those charges to transaction table.  But, here is
>>strange thing. When I run a totals query, sometimes there are two
>>records instead of one.
>>
>>For example; acct# 9-152 might show as:
>>Acct     SumOfTransactions
>>9-152     $20.00
>>9-152     $15.00
>>
>>instead of just:
>>9-152     $35.00
>>
>>Access sees "something" in the Alpha field sometimes.  In example
>>above, if I bring up those two records, and put cursour in the Alpha
>>field and backspace, or tab into field and delete, then Access will
>>still see them as two records.  But, if I put something in the Alpha
>>field, save record, go back and delete out what I just put in, then
>>Access now considers them the same and will give me just one total.
>
>Check the properties of the Alpha field. Is its "Allow Zero Length" property
>set to Yes? If so, it may contain either a NULL - undefined, no contents - or
>a zero length string - a precisely defined string value "". They'll be seen as
>different!
>
>It's also possible that you have some other nonprinting character. Try
>including a calculated field Len([ALPHA])  in a query to see if there is
>something other than null in the field. If there is you can use the Asc()
>function to get its ASCII value.


Yes, it was set to "Allow Zero Length".  I'll change that and see if
the problem reoccurs.

Thanks,
John
0
johnboy7676
4/7/2010 7:17:46 PM
On Wed, 7 Apr 2010 10:17:02 -0700, Daryl S
<DarylS@discussions.microsoft.com> wrote:

>Where and how is the acctnum created?  This may be the source of your issue.  
>I would check the code that creates the ACCTNUM and fix it.  That will 
>prevent future issues from coming up.
>
>Back up your database, then clean up the existing data.
>
>You should clean up your ACCTNUM data by trimming off any extra spaces at 
>the end of the string.  It could be that ALPHA is null, but your ACCTNUM is 
>not.  This would need to be cleaned up in both the master acct table and in 
>the billing charges table (and anywhere else the ACCTNUM is used).  
>
>Alternatively, do not use ACCTNUM from the tables at all, but 'build' that 
>value each time, and group on the built value.

I think "this" problem was that it was set to allow zero length, but I
agree that in general maybe I'd better rethink my method.

Thanks, John
0
johnboy7676
4/7/2010 7:18:58 PM
On Apr 7, 3:17=A0pm, johnboy7...@yahoo.com wrote:
> On Wed, 07 Apr 2010 11:02:16 -0600, John W. Vinson

> >It's also possible that you have some other nonprinting character. Try
> >including a calculated field Len([ALPHA]) =A0in a query to see if there =
is
> >something other than null in the field. If there is you can use the Asc(=
)
> >function to get its ASCII value.
>
> Yes, it was set to "Allow Zero Length". =A0I'll change that and see if
> the problem reoccurs.
>
> Thanks,
> John

Would that be considered an Alpha Blocker :-)?

James A. Fortune
MPAPoster@FortuneJames.com
0
James
4/7/2010 7:41:00 PM
On Wed, 07 Apr 2010 13:17:46 -0600, johnboy7676@yahoo.com wrote:

>Yes, it was set to "Allow Zero Length".  I'll change that and see if
>the problem reoccurs.
>

You'll also need to update the table replacing "" with Null in the records
which have it, if you do so.

-- 

             John W. Vinson [MVP]
0
John
4/7/2010 7:54:48 PM
johnboy7676@yahoo.com wrote in
news:1jmpr5tss3pcap8h2r5cqfb1lohugnouta@4ax.com: 

> On Wed, 7 Apr 2010 10:17:02 -0700, Daryl S
> <DarylS@discussions.microsoft.com> wrote:
> 
>>Where and how is the acctnum created?  This may be the source of
>>your issue.  I would check the code that creates the ACCTNUM and
>>fix it.  That will prevent future issues from coming up.
>>
>>Back up your database, then clean up the existing data.
>>
>>You should clean up your ACCTNUM data by trimming off any extra
>>spaces at the end of the string.  It could be that ALPHA is null,
>>but your ACCTNUM is not.  This would need to be cleaned up in both
>>the master acct table and in the billing charges table (and
>>anywhere else the ACCTNUM is used).  
>>
>>Alternatively, do not use ACCTNUM from the tables at all, but
>>'build' that value each time, and group on the built value.
> 
> I think "this" problem was that it was set to allow zero length,
> but I agree that in general maybe I'd better rethink my method.
> 
> Thanks, John
> 
What I would suggest is that the field be left at 'allow zero 
length', set the default to "" (the empty string), and set 
'required' to Yes, The required property prohibits nulls. 

Then I would create an unique index on the three fields. You might 
even set it to Primary key.




-- 
Bob Quintal

PA is y I've altered my email address.
0
Bob
4/7/2010 10:31:44 PM
Reply:

Similar Artilces:

Pivot Table Calculated field #3
I have a pivot table with columns for No's, Yes's and Grand Total. I would like to calculate the % of Yes's by dividing the Yes's by the Grand total. Can someone give me an easy explanation of how to do that or a source? I have tried every thing I can think of and the directions I have found have not helped.... Thanks, Ann To calculate total percent Yes, you could use the GETPIVOTDATA function in a formula outside the pivot table. For example: =GETPIVOTDATA("Region",$A$5,"Valid","Yes")/GETPIVOTDATA("Region",$A$5) MOE wrote: &g...

validate a field based on sum of other fields
I have to assign commission to agents for each "order". I can have anywhere from 1 agent at 100% commission to 5 agents with every possible combination of percentages. Obviously, I need the percentage entries to only add up to 100 and I'd like to force the user to adjust the figures until they total 100. Firstly, am I right in assuming it would be better to be assigning the agents to the orderIDs in separate records: OrderID, Agent, Agent Percentage (as opposed to having one record contain the OrderID, Agent1, Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Pe...

Locking Field position on screen.
I have created a simple form in Word 2007 with maybe 2 or 3 fields across and 3 or 4 fields down, but my problem is I would like to be able to lock the field positions in place on the screen. When I type in a field, the field to the left moves. I need to lock the position. Can this be done. Frank Most forms designers put form fields in table cells of a fixed dimension. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Francis Cunningham, Jr." <FrancisCunninghamJr@discussions.microsoft.com> wrote in m...

scripto to cut/paste to next field
Here is my situation: 1) A page on IE will be open, and I will have a cursor in a field on the page (field that has text in it) 2) I need to hit a shortcut (ctrl-shft-something) that cuts everything to the right of the cursor, advances a field (effectively, replicates ‘tab’ action), and pastes Anyone have some code (and instructions on what to do with the code) that may solve the above? -- Boris "BorisS" <BorisS@discussions.microsoft.com> wrote in message news:D4B54CE9-F2A4-4195-B2E3-ABA75A49202C@microsoft.com... > Here is my situation: > > 1...

text field to date field field
I have a database that has a text field called "visit" it has been filled with data ie., " 6/22/07 - not good, " the field may also be blank or it may contain random text after the date. In some cases no date at all just text. I would like to create a new field called DateVisit that contains just the dates that were entered in the field "visit" it has been a struggle and I have made no progress please help abbenj@verizon.net/nospam From your description, the field might contain a date, or a date and text, or text, or nothing. How would you explai...

When printing a contact the other field (Notes) get cut off
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: Exchange Also when just viewing the contact in the address book. <br> I have tried this on two different macbook pro's and the issue is the same. Tried a different printer also. Please help On 3/10/10 12:47 PM, mwilshrie@officeformac.com wrote: > Also when just viewing the contact in the address book. I have tried > this on two different macbook pro's and the issue is the same. Tried > a different printer also. Please help Can you post a picture of what you're de...

Form Fields In Outlook Template
Help !!! I'm not a developer, just a user, but I need some help. What I want to do is create an Outlook 2003 template, that uses form fields (Text, drop down boxes, etc.....) However, it looks like to properly create an Outlook template, you cannot use Word as your editor. But if I do not use Word as the editor, then I cannot access the Form Toolbar. This cannot be a new request - but I just cannot seem to figure this out. Help !!! Thank you See my response to your other post on this issue.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart...

Default fields in new fields
Using Outlook 2K When creating a new mail folder, it does not include the Subject and Received fields necessitating their having to be added every time. How can I get this to revert to the default fields? Thanks, Cathy ...

multiple critera for one field in a query
Hi, i am trying to get a count of a certain criteria but in different columns. Its hard to explain, but assume i have a table which consists of... A 550 B 600 C 20 A 100 C 75 B 120 D 390 Then in a query I am trying to have the result of: Over 500 Over 100 Over 25 Under 25 A 1 1 0 0 B 1 1 0 0 C 0 0 1 1 D 0 1 0 0 The way i have done...

required fields
Hello: Through either VBA or Modifier, is it possible to make a field that is required in Great plains by design to not be required but be optional instead? I doubt it. The best that I could come up with was to just have VBA fill the required field with a "dummy" piece of information and move on. Any other ideas? I suppose the only other way is through Dexterity. The field is the "Interview Range from 1 to" in the HR Interview Setup window. I just decided to have VBA fill it with "01". Thanks! childofthe1980s You are right, fields Required by GP cann...

IV00102 Fields
These four fields are set to zero when I create a new item and assign it to a location. However, when I look at the table descriptions zero is not a valid value for any of them. Are these four fields currently used for anything or are they there for future use? This currently is GP10 SP3. 1) IV00102.REPLENISHLEVEL 2) IV00102.POPVENDORSELECTION 3) IV00102.POPPRICINGSELECTION 4) IV00102.PICKTICKETITEMOPT Rich, Replenishment Level is the default replenishment level for POP Auto Generator. Values are 1, 2 and 3. 1 for Order point QTy, 2 for order upto level, 3 for vendor EoQ. If these fiel...

Field by Field Comparison
I have two tables with the exact same column names. I want to do a field by field comparison of the two tables. If one letter or number is off in any field, I would like for the whole record to show up in my query. I can only figure out how to show records which are equal. I would like to show records which are not equal. Is there a query criteria or something where I can perform this? Thanks. Do a UNION query. Create a query that lists all the field in one table. Then open that query in SQL view. At the end of the SQL, delete the semicolon ";". At the...

splitting a field into two fields; one in ()
I have a Name field in my database that is LastName,FirstName(dept). I have figured out how to split last name and first name but I don't know how to leave out the department name that is in () after the first name. Any suggestions? Quick and Dirty way to get the field down to just LastName,Firstname is to use the following as an expression in a query: LastFirst:Left([Name],Instr([Name],"(")-1) This should give you all of the characters to the left of the opening parenthesis. You could then apply your method for splitting the First and Last Names. HTH ...

Joining fields into one field dependent on value of another field
Hi, Below is a sample of data of a query which I need assistance in - Group Fertiliser ApplicationRate 1 TSP 3 2 EFB 230 3 Kies. 3 4 EFB 230 4 Kies. 3 5 TSP 3 5 Kies. 3 7 EFB 230 7 TSP 3 7 Kies. 3 >From this, I would like to create a field such that it shows eg. Group FertiliserDescription 1 TSP (3) 2 EFB (230) 3 Kies. (3) 4 EFB (230), Kies(3) 7 EFB(230),TSP(3),Kies(3) I am able to put Fertiliser and ApplicationRate together ie. Fertiliser& "(" &ApplicationRate& ")" My problem being how to add a statement saying th...

How to set color of field?
In ObjectPal I used the 'Arrive' event to set the color of a field or a record in a child table. This allowed the color to vary from one record to the next in a datasheet view. In Access, the 'On Current' event only works in form view. It doesn't work in datasheet view. The 'Format' event is only available in Report objects. The 'Conditional' format only allows three alternatives. How can Access vary colors from one record to the next in a datasheet, or datasheet-like view? "WDSnews" <wdsnews.0640@oregoncity.com> wrot...

Null Field to Interupt Closing
I am using this code to check for blank fields in BeforeUpdate: If NZ(Trim(Me!Date),vbnullstring) = vbnullstring then Msgbox "PLEASE ENTER DATE",,"Missing date" Me!Date.setfocus Cancel = True Exit Sub End if It almost works, except the form goes ahead and closes anyway. The user clicks the close button and the message box pops up if the field is empty, they click OK and instead of keeping the form open and then setting focus on the empty field, it closes the form. How can I get it to interupt the close and keep the form open? Thank you in advance for any...

Problem with after update code in field that disables another field
Hi I want to be able to disable the Non Member field in a record when something is entered in the Member field. Unfortunately, the code I am usng below disables the Non Member field in *all* records, and the database has to be closed and reopened to get round this before data entry can continue. I'd be grateful if someone could show me the correction, Thanks. Private Sub Member_AfterUpdate() If Not IsNull([Member]) Then Me![Non Members].Enabled = False Else Me![Non Members].Enabled = True End If End Sub Sam, You need to put that same code in on the OnCurrent event of the form...

Field name derived from a string value returns a Run-time error 2465.
I have two labels on a form and want to populate them during the run time of my form. The name of the labels are "1" the other is called "2" (without the quotes of course). Field 1 runs OK but two returns a run-time error 2465. Of course, the real form is much bigger. A lot of fields called 1 through 31 are populated from within a loop. To simplify my question I down sized this post to two label fields. Any one know how to solve the problem with label "2"? Any help or hint is appreciated. ' This works OK Me![1].Caption = "abc"...

Values in Pay To Field
How do I remove values from the Pay To list? Through syncing with my PDA, a number of the valkues have become corrupted and I want to remove them In microsoft.public.money, Mike McLellan wrote: >How do I remove values from the Pay To list? Through syncing with my PDA, a >number of the valkues have become corrupted and I want to remove them Go to Banking->AccountTools->Categories&Payees->Payees, right-click a Payee and select Delete the selected Payee from the drop-list. Alternatively use the Delete button at the bottom to deleted the selected Payee from the drop-list....

ReportViewer
Using ReportViewer control v9 in .aspx page - When displaying date type parameter field in my report nothing happens when calendar icon is clicked (no calendar page, no error) in ie 8. FoxFire works. Same report works fine when viewed directly on the report server in ie8. Resolved myself. Sloppy coding on the Master page. <form> tag out of place in table structure. "Pete Hay" wrote: > Using ReportViewer control v9 in .aspx page - When displaying date type > parameter field in my report nothing happens when calendar icon is clicked > (no calen...

Updating a field in a Table with a subform field
Hi, I have a form that has a subform. I would like to update the Table that is bound to my form with the info from the subform once the use is done entering the info on the main form. Ex. I want the stockcode field in the subform to update in the stockcode field in the main form's table. Thank You Linda On Thu, 26 Nov 2009 09:33:03 -0800, linda <linda@discussions.microsoft.com> wrote: >Hi, > >I have a form that has a subform. I would like to update the Table that is >bound to my form with the info from the subform once the use is done entering ...

Required Fields
We were planning on making 1/3 address types required: phone, email or address. Is there any way to do this? One idea : 1°) You add a picklist "Contact Method" with 3 values : 'Phone', 'Email', 'Address' 2°) You code an OnChange method which force the user to enter a value in the field 'Phone' if the value of "Contact Method" is 'Phone'... According to me, there is no other method. Good luck -- Michael MARTIN BrainSoft (France) "erinm" wrote: > We were planning on making 1/3 address types required: p...

bound unbound fields into newrecord vb2005
hi, I'am trying to make an vb2005 application.I've a connection with a accessdatabase. on a winform i've some field which were scanned and are displayed on a winform. I compare this data with the data in the database on the same form, in a dtagridview. However , when i've receive totaly new data, i've nothing to compare , meaning i have a new client. how can i add these new data which are displayed in unbound textfields into a new record in the database ? regards, Jef ...

field based on field
I feel like I've asked this before. How do I make a field based on another field in the same query? exp1=a+b exp2=val(expr1) How do I write expr2? Kou On Thu, 13 Dec 2007 09:01:02 -0800, Kou Vang wrote: > I feel like I've asked this before. How do I make a field based on another > field in the same query? > > exp1=a+b > > exp2=val(expr1) > > How do I write expr2? > > Kou Your above question can have more than one result depending upon field datatype and what you mean by using the "+". Did you want to concatenate or add? Since your...

find all records in date field
How do I find all records for a field "Birthdate" for particular month Use a query, and in the "Criteria" box, put this (change dates to suit, and use the normal format for your location, eg 12/25/2009 instead of 25/12/2009) Between #01/01/2009# and #25/12/2009# Phil, London "Keith" <Keith@discussions.microsoft.com> wrote in message news:7E8ECAAA-D7DA-4F65-8ED7-80C4AEE28963@microsoft.com... > How do I find all records for a field "Birthdate" for particular month SELECT * FROM YourTable WHERE Month(BirthDate) = [Enter the...