Results 1 to 5 of 5
  1. #1
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27

    End if issue

    Hey there,

    I'm a noob and here is my issue. I'm importing from excel, the fields either say TRUE OR FALSE once entered, Access for some reason converts to 0 and -1, I just want the 0 to say N and if it says anything other in both of these fields shown below to enter a Y...Here is the code I am trying. Also, I only know how to enter the code to run on a form and didn't know if I could have it run it after the table is imported rather than go through each record. Thanks to everyone for any help!

    Private Sub Form_Load()
    If Me.Leak_Found = 0 Then Me.Leak_Found = "N"


    Else
    Me.Leak_Found = "Y"
    End If
    If Me.Atmospheric_Corrosion_Found = 0 Then Me.Atmospheric_Corrosion_Found = "N"
    Else
    Me.Atmospheric_Corrosion_Found = "Y"
    End If
    End Sub

  2. #2
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Hi,

    A little background, TRUE and FALSE are boolean, like binary on and off, in a database this is wasteful of characters so it records it as 0 (False) and -1 (True).

    The table you are trying to change the data on (by using the vb on a form in this case) most has its fields all set to true/false.

    You have a few options available:
    1. don't change the data! (my personal option)v
    Sounds crazy, however; you have the data there already, you just don't like what you see, so setup controls on your form, for instance.
    txt_Leak_found and txt_Atmospheric_corrosion refer to fields on your form.

    txt_Leak_found Controlsource: =iif([leak_found]=true,"Y","N")

    txt_Atmospheric_corrosion Controlsource: =iif([Atmospheric_corrosion]=true,"Y","N")

    If you have trouble, I'll create an example to demonstrate.

    The other options I'll reserve for now to prevent a wall of text.

  3. #3
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    You are awesome! I figured it out before I saw this so I'm proud of myself that I wasn't doing it half assed...it just changed the name of the table fields and I had to adjust that but now I am faced with another problem. Thank you so much for taking the time you did. Seriously, it's nice to have a place where people like yourself who arent required but take time for people like me...Anyway, now I have a date in excel that shows 3/26/2012 and when I export to a text file I want it to show 03262012...If you know how to do that, I will mail you a thank you card to programming heaven!

    A little background, the text file needs to have a required number of (spacing or bytes) and the date needs 8 bytes but because of the lacking ZERO and two HASH marks it is throwing off the entire text file. I can hopefully enter that expression into the query as I did the expression above. Thanks again!

  4. #4
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    Actually got that one too in Access to reflect as formatting as MMDDYYYY but it still shows original format when i export to the text file as 3/26/2012..

  5. #5
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Im not expert on excel but the best thing to do in access is to create a query containing your date, then put in a calculation

    Fixed_date: =format([date_field_name_here],"mmddyyyy")

    then when you export that *should* come out as you are after!

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

Similar Threads

  1. VBA issue
    By manic in forum Programming
    Replies: 4
    Last Post: 02-28-2012, 03:57 PM
  2. IIF issue
    By mohiahmed in forum Queries
    Replies: 9
    Last Post: 01-22-2012, 01:06 AM
  3. VBA Issue
    By MikeDBMan in forum Access
    Replies: 9
    Last Post: 08-02-2011, 03:59 PM
  4. Issue with Do
    By Petefured in forum Programming
    Replies: 1
    Last Post: 05-25-2011, 09:27 AM
  5. new issue...graphs
    By thewabit in forum Forms
    Replies: 3
    Last Post: 02-01-2010, 05:19 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
  •  
Other Forums: Microsoft Office Forums