Page 1 of 2 12 LastLast
Results 1 to 16 of 17

Thread: how to search for nothing in Access

  1. #1
    boop, got your nose stevie lee's Avatar
    Join Date
    Sep 2007
    Location
    southport
    Posts
    2,670
    Thanks
    414
    Thanked
    422 times in 318 posts
    • stevie lee's system
      • Motherboard:
      • ASUS ROG STRIX B450-F Gaming
      • CPU:
      • Ryzen 3600 (stock cooler)
      • Memory:
      • 16 GB Corsair 3600 MHZ Cas 18
      • Storage:
      • Crucial 250GB BX500, Hitachi 640GB + 1TB. WDblue 2TB, Crucial M500 240GB
      • Graphics card(s):
      • sapphire 7700
      • PSU:
      • bluestorm II 400w
      • Case:
      • Xigmatech Midgard
      • Operating System:
      • Win 10 Home
      • Monitor(s):
      • 22" Samsung SM2233BW
      • Internet:
      • 20mb plusnet unlimited!

    how to search for nothing in Access

    using Access 2003 with no service packs on XP sp1.

    the database is a record of every job that we have done in the workshop, with how much the job costs in one field and whether its been paid for in another.

    we want a quick way of checking whether theres any records without any data in the 'has been paid' field.

    how would you write a query to find and list all the blank entries? whats the formula?

  2. #2
    Senior Member chrestomanci's Avatar
    Join Date
    Sep 2004
    Location
    Reading
    Posts
    1,614
    Thanks
    94
    Thanked
    96 times in 80 posts
    • chrestomanci's system
      • Motherboard:
      • Asus AMD AM4 Ryzen PRIME B350M
      • CPU:
      • AMD Ryzen 1600 @ stock clocks
      • Memory:
      • 16Gb DDR4 2666MHz
      • Storage:
      • 250Gb Samsung 960 Evo M.2 + 3Tb Western Digital Red
      • Graphics card(s):
      • Basic AMD GPU (OSS linux drivers)
      • PSU:
      • Novatech 500W
      • Case:
      • Silverstone Sugo SG02
      • Operating System:
      • Linux - Latest Xubuntu
      • Monitor(s):
      • BenQ 24" LCD (Thanks: DDY)
      • Internet:
      • Zen FTTC

    Re: how to search for nothing in Access

    I have not done anything like that in access, but in another database I use, the query expression would be something like:
    Code:
    not has_attr('has_been_paid')
    This would match on all records that don't have the has been paid attribute.

    Obviously the syntax in access will be different, but you should be able to find the correct syntax from the documentation or online.

  3. Received thanks from:

    stevie lee (23-08-2009)

  4. #3
    Senior[ish] Member Singh400's Avatar
    Join Date
    Jun 2008
    Posts
    2,933
    Thanks
    136
    Thanked
    310 times in 247 posts

    Re: how to search for nothing in Access

    Try IsNull I think, thats of the top of my head. We use it in a few queries at work, I double check for you when I get into work tomorrow morning.

  5. Received thanks from:

    stevie lee (23-08-2009)

  6. #4
    Senior Member
    Join Date
    Jun 2008
    Posts
    1,474
    Thanks
    2
    Thanked
    140 times in 116 posts
    • BobF64's system
      • Motherboard:
      • Asus P8Z77-V Pro
      • CPU:
      • Intel Core i7-3770K
      • Memory:
      • 16GB Corsair XMS3 PC3-12800
      • Storage:
      • Multiple HDD and SSD drives
      • Graphics card(s):
      • ASUS DUAL-GTX1060-06G
      • PSU:
      • 750W Silverstone Strider Gold Evolution
      • Case:
      • Silverstone Fortress FT02
      • Operating System:
      • Windows 10 x64 Pro
      • Monitor(s):
      • HP ZR24w

    Re: how to search for nothing in Access

    You could try using IsNull, that should find fields with no data entered.

  7. Received thanks from:

    stevie lee (23-08-2009)

  8. #5
    boop, got your nose stevie lee's Avatar
    Join Date
    Sep 2007
    Location
    southport
    Posts
    2,670
    Thanks
    414
    Thanked
    422 times in 318 posts
    • stevie lee's system
      • Motherboard:
      • ASUS ROG STRIX B450-F Gaming
      • CPU:
      • Ryzen 3600 (stock cooler)
      • Memory:
      • 16 GB Corsair 3600 MHZ Cas 18
      • Storage:
      • Crucial 250GB BX500, Hitachi 640GB + 1TB. WDblue 2TB, Crucial M500 240GB
      • Graphics card(s):
      • sapphire 7700
      • PSU:
      • bluestorm II 400w
      • Case:
      • Xigmatech Midgard
      • Operating System:
      • Win 10 Home
      • Monitor(s):
      • 22" Samsung SM2233BW
      • Internet:
      • 20mb plusnet unlimited!

    Re: how to search for nothing in Access

    i'll have a look at that IsNull then and also try the suggestion from chrestomanci, just to see what happens.

    if there are any other methods apart from this one i just thought of then suggest away -

    for the people using the database its too complicated for them.
    : open the table in datasheet view.
    :select the 'has been paid' column.
    :A-Z sort.
    :the blank ones should be at the top or bottom.

  9. #6
    Senior Member chrestomanci's Avatar
    Join Date
    Sep 2004
    Location
    Reading
    Posts
    1,614
    Thanks
    94
    Thanked
    96 times in 80 posts
    • chrestomanci's system
      • Motherboard:
      • Asus AMD AM4 Ryzen PRIME B350M
      • CPU:
      • AMD Ryzen 1600 @ stock clocks
      • Memory:
      • 16Gb DDR4 2666MHz
      • Storage:
      • 250Gb Samsung 960 Evo M.2 + 3Tb Western Digital Red
      • Graphics card(s):
      • Basic AMD GPU (OSS linux drivers)
      • PSU:
      • Novatech 500W
      • Case:
      • Silverstone Sugo SG02
      • Operating System:
      • Linux - Latest Xubuntu
      • Monitor(s):
      • BenQ 24" LCD (Thanks: DDY)
      • Internet:
      • Zen FTTC

    Re: how to search for nothing in Access

    Quote Originally Posted by stevie lee View Post
    i'll have a look at that IsNull then and also try the suggestion from chrestomanci, just to see what happens.
    Which one works will depend on how your access database sets up records.

    I am using an informix database, where each object can have an arbitrary set of attributes. Some objects have just a few attributes, others might have a hundred. The business logic sitting above informix decides what types of object need which of attributes (where the type is just another attribute), and tie the whole lot together into a useful application.

    The alternative way of doing things is as cardfile records. This is like a set of index cards where each card has a fixed set of attributes usually of fixed length. You can only define the list of attributes, and their lengths and types when the database is first created. In one of these databases and empty attribute would have some sort of null stored there.

    The first type of databse is known as a relational database, and usually speaks some sort of SQL, They are powerful, but expensive to buy and more complex to understand and administer. In comparison cardfile databases are simpler to understand and cheap to buy and setup, and are often sufficient for simple databases such as an address book.

    Quote Originally Posted by stevie lee View Post
    if there are any other methods apart from this one i just thought of then suggest away -

    for the people using the database its too complicated for them.
    : open the table in datasheet view.
    :select the 'has been paid' column.
    :A-Z sort.
    :the blank ones should be at the top or bottom.
    That would work fine until your have 100,000 records, and you are looking for the 10 who have not paid.

  10. Received thanks from:

    stevie lee (24-08-2009)

  11. #7
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Re: how to search for nothing in Access

    Im not sure of the syntax, but in some PHP/Mysql work I've done, I've had to find 'null' results by searching for a string length of 0 on the required field.

  12. #8
    PHP Geek Flash477's Avatar
    Join Date
    Dec 2008
    Location
    Devon
    Posts
    822
    Thanks
    51
    Thanked
    72 times in 65 posts

    Re: how to search for nothing in Access

    It depends on the type of the field and how well the data has been conntrolled.

    I would have thought that the field would be a Yes/No or numeric field controlled by a tickbox, in which case it would be:

    Code:
    SELECT * FROM Table WHERE PaidFor = 0
    otherwise if it is a string or date field it would either be:

    Code:
    SELECT * FROM Table WHERE PaidFor IS NULL
    or if some of the fields contain empty strings (ie. it is not null, but still empty) then you would need

    Code:
    SELECT * FROM Table WHERE PaidFor IS NULL or PaidFor = ''

  13. Received thanks from:

    stevie lee (24-08-2009)

  14. #9
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Gateshead
    Posts
    15,196
    Thanks
    1,230
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p laptop panel

    Re: how to search for nothing in Access

    Quote Originally Posted by Flash477 View Post
    It depends on the type of the field and how well the data has been conntrolled.

    I would have thought that the field would be a Yes/No or numeric field controlled by a tickbox, in which case it would be:

    Code:
    SELECT * FROM Table WHERE PaidFor = 0
    otherwise if it is a string or date field it would either be:

    Code:
    SELECT * FROM Table WHERE PaidFor IS NULL
    or if some of the fields contain empty strings (ie. it is not null, but still empty) then you would need

    Code:
    SELECT * FROM Table WHERE PaidFor IS NULL or PaidFor = ''
    This - spot on (well, Access uses double quotes to delimit strings, not single, but otherwise spot on )

    It does depend on the data type of the 'not paid' field - if you can look at the table design you can check the type and whether there's a default value (if there is a default value you shouldn't have any blanks, of course... then you need to search for the default value)

  15. Received thanks from:

    stevie lee (24-08-2009)

  16. #10
    boop, got your nose stevie lee's Avatar
    Join Date
    Sep 2007
    Location
    southport
    Posts
    2,670
    Thanks
    414
    Thanked
    422 times in 318 posts
    • stevie lee's system
      • Motherboard:
      • ASUS ROG STRIX B450-F Gaming
      • CPU:
      • Ryzen 3600 (stock cooler)
      • Memory:
      • 16 GB Corsair 3600 MHZ Cas 18
      • Storage:
      • Crucial 250GB BX500, Hitachi 640GB + 1TB. WDblue 2TB, Crucial M500 240GB
      • Graphics card(s):
      • sapphire 7700
      • PSU:
      • bluestorm II 400w
      • Case:
      • Xigmatech Midgard
      • Operating System:
      • Win 10 Home
      • Monitor(s):
      • 22" Samsung SM2233BW
      • Internet:
      • 20mb plusnet unlimited!

    Re: how to search for nothing in Access

    well me and the IT manager had a look today behind the scenes, she nearly had a heart attack at the complete mess it was.

    we have no idea how the database is actualy working. the data doesnt seem to be stored in a table - mainly because there aren't any - it appears to be stored in the form itself.

    all the data is actually in the database, from the looks of it most of the formulas are wrong aswell.

    couldn't try the methods posted above, it was that much of a mess



    we are not going to use the database at all now. we've decided to go back to pen and paper, its easier

    thanks for all your help folks,

    i'll pass the information on to the IT manager, who might be having a go at writing a completely new database, can add it in from the start then

  17. #11
    Senior[ish] Member Singh400's Avatar
    Join Date
    Jun 2008
    Posts
    2,933
    Thanks
    136
    Thanked
    310 times in 247 posts

    Re: how to search for nothing in Access

    Quote Originally Posted by stevie lee View Post
    well me and the IT manager had a look today behind the scenes, she nearly had a heart attack at the complete mess it was.

    we have no idea how the database is actualy working. the data doesnt seem to be stored in a table - mainly because there aren't any - it appears to be stored in the form itself.

    all the data is actually in the database, from the looks of it most of the formulas are wrong aswell.

    couldn't try the methods posted above, it was that much of a mess

    we are not going to use the database at all now. we've decided to go back to pen and paper, its easier
    What? Data is stored in the forms? No way Open the said form, and go into Design View, then check the form properties, and see what table/qry it is built off. Then trace it back.

  18. #12
    boop, got your nose stevie lee's Avatar
    Join Date
    Sep 2007
    Location
    southport
    Posts
    2,670
    Thanks
    414
    Thanked
    422 times in 318 posts
    • stevie lee's system
      • Motherboard:
      • ASUS ROG STRIX B450-F Gaming
      • CPU:
      • Ryzen 3600 (stock cooler)
      • Memory:
      • 16 GB Corsair 3600 MHZ Cas 18
      • Storage:
      • Crucial 250GB BX500, Hitachi 640GB + 1TB. WDblue 2TB, Crucial M500 240GB
      • Graphics card(s):
      • sapphire 7700
      • PSU:
      • bluestorm II 400w
      • Case:
      • Xigmatech Midgard
      • Operating System:
      • Win 10 Home
      • Monitor(s):
      • 22" Samsung SM2233BW
      • Internet:
      • 20mb plusnet unlimited!

    Re: how to search for nothing in Access

    Quote Originally Posted by Singh400 View Post
    What? Data is stored in the forms? No way Open the said form, and go into Design View, then check the form properties, and see what table/qry it is built off. Then trace it back.
    we did that.

    we couldnt find ANY tables with any information in whatsoever.

    cant post the database up so you can have a look, theres too much confidential info in it.
    Last edited by stevie lee; 24-08-2009 at 06:40 PM.

  19. #13
    Senior[ish] Member Singh400's Avatar
    Join Date
    Jun 2008
    Posts
    2,933
    Thanks
    136
    Thanked
    310 times in 247 posts

    Re: how to search for nothing in Access

    Quote Originally Posted by stevie lee View Post
    we did that.

    we couldnt find ANY tables with any information in whatsoever.

    cant post the database up so you can have a look, theres too much confidential info in it.
    Is it split into a FrontEnd & BackEnd?

    Maybe the tables are hidden or used that old trick to make them a System Hidden Object?

  20. #14
    boop, got your nose stevie lee's Avatar
    Join Date
    Sep 2007
    Location
    southport
    Posts
    2,670
    Thanks
    414
    Thanked
    422 times in 318 posts
    • stevie lee's system
      • Motherboard:
      • ASUS ROG STRIX B450-F Gaming
      • CPU:
      • Ryzen 3600 (stock cooler)
      • Memory:
      • 16 GB Corsair 3600 MHZ Cas 18
      • Storage:
      • Crucial 250GB BX500, Hitachi 640GB + 1TB. WDblue 2TB, Crucial M500 240GB
      • Graphics card(s):
      • sapphire 7700
      • PSU:
      • bluestorm II 400w
      • Case:
      • Xigmatech Midgard
      • Operating System:
      • Win 10 Home
      • Monitor(s):
      • 22" Samsung SM2233BW
      • Internet:
      • 20mb plusnet unlimited!

    Re: how to search for nothing in Access

    can send you a blank copy to have a look if you want. just found it on my flash drive.

  21. #15
    Senior[ish] Member Singh400's Avatar
    Join Date
    Jun 2008
    Posts
    2,933
    Thanks
    136
    Thanked
    310 times in 247 posts

    Re: how to search for nothing in Access

    Quote Originally Posted by stevie lee View Post
    can send you a blank copy to have a look if you want. just found it on my flash drive.
    Sure if you want mate.

  22. #16
    boop, got your nose stevie lee's Avatar
    Join Date
    Sep 2007
    Location
    southport
    Posts
    2,670
    Thanks
    414
    Thanked
    422 times in 318 posts
    • stevie lee's system
      • Motherboard:
      • ASUS ROG STRIX B450-F Gaming
      • CPU:
      • Ryzen 3600 (stock cooler)
      • Memory:
      • 16 GB Corsair 3600 MHZ Cas 18
      • Storage:
      • Crucial 250GB BX500, Hitachi 640GB + 1TB. WDblue 2TB, Crucial M500 240GB
      • Graphics card(s):
      • sapphire 7700
      • PSU:
      • bluestorm II 400w
      • Case:
      • Xigmatech Midgard
      • Operating System:
      • Win 10 Home
      • Monitor(s):
      • 22" Samsung SM2233BW
      • Internet:
      • 20mb plusnet unlimited!

    Re: how to search for nothing in Access

    scratch that idea,

    managed to corrupt the whole thing by deleting the info out. wont even open now.

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 35
    Last Post: 08-01-2009, 04:57 PM
  2. old xp installation user access
    By vincent4yuffie in forum Help! Quick Relief From Tech Headaches
    Replies: 3
    Last Post: 03-11-2006, 11:14 AM
  3. Replies: 0
    Last Post: 20-10-2006, 12:53 PM
  4. NT4 Domain - A: drive access
    By joshwa in forum Software
    Replies: 1
    Last Post: 27-08-2003, 09:07 AM

Posting Permissions

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