Solved

MS Access VBA Need help with statement to control another field

Posted on 2013-01-28
9
221 Views
Last Modified: 2013-01-28
Why doesn't this code work?

 If IsDate(Me.AendDate) = True Then -----------------This part works fine.
        Me.Status = "Completed"
    Else
If IsDate(Me.AendDate) = False Then -----------------This part doesn't work. If date is deleted,
        Me.Status = "Working"                      I want the status field to change to "Working".
       End If
    End If
End Sub
0
Comment
Question by:DJPr0
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 6

Accepted Solution

by:
nickinthooz earned 500 total points
ID: 38827941
take out

If IsDate(Me.AendDate) = False Then

You're using else and a boolean so it can only be true or false, if it's not true then it's got to be false?
 If IsDate(Me.AendDate) = True
        Me.Status = "Completed"
    Else
        Me.Status = "Working"                
       End If

Open in new window

0
 

Author Comment

by:DJPr0
ID: 38828000
Only the first part works (Completed)

Does the After Update kick in when the date is deleted?

The "Completed" doesn't change to "Working" when the date is deleted.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38828031
try something like this

 If IsDate(Me.AendDate) = True and me.AendDate & ""<>"" then
        Me.Status = "Completed"
    Else
        Me.Status = "Working"                
       End If
0
 
LVL 6

Expert Comment

by:nickinthooz
ID: 38828047
if you delete the date, you need to put the code somewhere it can check, like the on change event or after the focus to the date field is lost.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:DJPr0
ID: 38828053
Same problem - Works only for "Completed".
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38828069
DJPro
where did you placed the codes ?

and when responding, pls, mention the expert to whom you are responding.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38828079
Private Sub aenddate_AfterUpdate()
If IsDate(Me.aenddate) Then
Me.status2 = "Completed"
Else
Me.status2 = "working"
End If
End Sub

works for me after leaving aenddate after a deletion.
0
 
LVL 6

Expert Comment

by:nickinthooz
ID: 38828087
what about

 If IsDate(Me.AendDate) = True
        Me.Status = "Completed"
    Elseif  isnull(me.[fieldname]) then
        Me.Status = "Working"                
       End If

Open in new window


change your fieldname to whatever your fieldname is.
0
 

Author Closing Comment

by:DJPr0
ID: 38828141
I had some conflicting code that ran above this statement.

Sorry for the confusion.

The first post works fine.

Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now