Results 1 to 9 of 9

Thread: looping....

  1. #1
    Join Date
    Mar 2004
    Posts
    77

    Unanswered: looping....

    yoz ppl. here's the situation, i got 8 fields name Year1, Year2 ... and so forth until Year8. Now i need to check thru every1 of them 2 see if they are null. this is what i have in mind:

    dim count as integer
    dim store as string

    count = 1

    do until count = 9
    if isnull(Year&count) then
    store = Year&count
    exit do
    else
    count = count + 1
    end if
    loop

    the problem encountered here is that the Year and count concatenation does not work. so anyone got any idea how to concatenate the Year and count together so that it will come out as Year1, ...., Year8?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    where are these fields?
    form or table??
    one record or many??

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    Posts
    77
    these fields are on a form. if u r asking how many of these records i'm checking, my answer is a lot. almost 500 records needs to be checked. any ideas?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this should illustrate the process for the current record on a form:

    Dim ctlName As String
    dim intLoop as integer
    dim msg as string
    for intLoop = 1 to 8
    ctlName = "Year" & intLoop
    if isnull(Me.Controls(ctlName)) then msg = msg & ctlName & ", "
    next
    if len(msg) > 0 then msgbox "The following controls are NULL: " & msg

    if you are playing with the whole table, then you would use fields in a recordset to achieve a similar result.
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...but for the whole table, a query looking for IS NULL would be more efficient... you could select into a form, report, UPDATE or whatever.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yeah I know I'm famous for the left-field questions but...

    What is this for? Why do you have eight fields for different years? There may be a better way to do this...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Teddy

    you're right... there IS a better way, but how many times a day can you try to persuade folk to get their tables straight.

    what do we talk about on this forum: wrong concatenation; combo/list handling; patching up bent tables... that doesn't leave a lot of fun stuff to play with.

    this one was more fun with .controls()

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Depending on his answer to my question, your solution may actually be the way to go. Even if it was merely an attempt to escape the IT grind
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Mar 2004
    Posts
    77
    hey ppl, thx for the ideas. see that u all have been sort of arguing which is better. reason why i need to check for nulls using VBA is that it must be done in real time. and to me, using the query method seems to have the need to increase the number of forms. unless u guys have ideas on transfering the results of the query to the form without going thru another form and also cut down the ISNULL query to juz one query...

    and Teddy, 8 different fields coz all the entries will have different values, so i can't create another table to link them. it will be a waste of db size and also have the same problem.

    gonna try izy's VBA method....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •