Results 1 to 14 of 14
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    is null not working.....

    I have a field in my form that will only show one of the following values:
    CDN


    USD
    (NULL) - meaning be blank - empty field

    I'm trying to create an if statement as follows:

    ([Forms]![fFindDups2]![FIle Curr]="CDN" And [Forms]![Profit Tracking 2]![Text108]="USD") Or ([Forms]![fFindDups2]![FIle Curr]="USD" And [Forms]![Profit Tracking 2]![Text108]="CDN") Or ([Forms]![fFindDups2]![FIle Curr] Is Null)

    This returns the following error "Type mismatch" if the field is blank/empty

    This works perfect if i leave out the last .....Or ([Forms]![fFindDups2]![FIle Curr] Is Null)

    Can someone suggest how to write this if statement to include a blank/empty field?

    thanks,
    STeve

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Where are you using this expression? Show the complete expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Please see the image showing the full expression. I'm using this in a CLOSE FORM button. The button will run this macro (on click property) then close the form .


    Click image for larger version. 

Name:	Capturex.JPG 
Views:	22 
Size:	62.5 KB 
ID:	38129

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, Is Null should work in that context. I cannot replicate issue. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In a macro maybe it's IsNull() and not Is Null. AFAIK, Is Null is only for queries but since I don't use macros, can't say what it will accept.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That was my initial thought so tested Is Null in macro If and it worked. Cannot replicate issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @fishhead - when a macro generates an error, single step through it and report which command generates the error. As far as we can tell from here, the problem might be your form opening or record saving portion and not the last OR parameter because you didn't identify the offending macro line.
    You can also post a db as June7 noted.

  8. #8
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Quote Originally Posted by Micron View Post
    In a macro maybe it's IsNull() and not Is Null. AFAIK, Is Null is only for queries but since I don't use macros, can't say what it will accept.

    I tried ISNull() and now get a syntax error... i'll try to upload the dbase as you suggest.

    thanks guys.

  9. #9
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    loading attachment

    I seem to be having difficulty loading the attachment zipped folder.
    I add files etc and it shows it's uploading but when done the zipped folder doesn't appear anywhere.....

    what am i doing wrong?

  10. #10
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    I seem to be able to drag the file over but not the zipped folder containing the folder.
    the file is much larger and will take some time......

  11. #11
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    If i attach the file it appears to be too large and won't load into the uploaded files section....the utility doesn't appear to allow me to upload the smaller zipped folder?

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    as for uploading, see if this helps https://www.accessforums.net/showthread.php?t=70301
    Your file has to be (I believe) under 2 Mb. Usually you have to zip it to get it down to size.
    If need, be you would have to remove unnecessary stuff from the db, such as forms, reports that are not required for testing your issue.

    You don't write IsNull(), you write IsNull(some reference) where some reference is the complete reference to whatever you're testing for null.
    Last edited by Micron; 04-18-2019 at 08:57 AM. Reason: added info

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    just looking at your logic it would appear that you could shorten your code by using <> (not equal) rather than checking for = .
    you'd be doing one comparison instead of two.

    Code:
    if Me.[FIle Curr] <> Me.text108 or isnull(Me.[File Curr]  then ...
    btw, you should really eliminate any spaces in your field names.



  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Eliminate spaces in ALL names. Also no punctuation/special characters (underscore only exception). And no reserved words as names for anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA Update not working for null values
    By jslings in forum Forms
    Replies: 4
    Last Post: 04-04-2019, 06:13 PM
  2. Nz null not working in report from a combobox
    By caniread in forum Reports
    Replies: 2
    Last Post: 02-12-2016, 10:48 PM
  3. Is Null Not working in access query
    By Japn in forum Access
    Replies: 13
    Last Post: 05-04-2015, 10:56 AM
  4. Working with queries and null values
    By guidout in forum Queries
    Replies: 6
    Last Post: 03-26-2015, 05:53 PM
  5. IIF NULL not working
    By krutoigoga in forum Reports
    Replies: 6
    Last Post: 05-12-2010, 01:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums