AndAlso equivalent in VBA?

[Heatshiver]

I have a piece of code that requires a logical operator like AndAlso in VB.net.

I can circumvent this by creating another form, but I am trying to reuse forms where possible as there will be many more to add later.

The code is a simple If statement with the first line involving both a DLookup for date validation and a second part checking if a field is locked (I've also tried enabled).

I tried using the And operator, and it gives me an error to the DLookup part (and rightfully so).

I just need the code to check to make sure both parts of the statement are true, and if not, to proceed with the rest of the code statements.

 

[pr2-eugin]

Can you show the code you have used? and also the exact error?

 

[Mr. B]

Rather than just do a DLookup for the date validation, you may need to first make sure that a record or the data exists. If the data does not exist, you Dlookup will cause an error and that may be what is causing part of your problem.

So I would suggest that you try to use Dcount to determine if there is a record with the date. Then if the data exists, declare a variable to hold the results of you Dlookup.

Then you can create your "IF" statement using the AND operator to compare both pieces of information.

As long as your code is saying:
"IF a certain condition = some condition AND a certain condition = some condition THEN"

You should be able make the comparison you need.

Hope this helps.

 

[Heatshiver]

The problem is that that date field is locked depending on the options selected from the previous form, so no date value will be entered.

 

[Heatshiver]

I have duplicated the form and taken out the piece of code. This works, but I would like to find a method to skip over this if possible.

 

[gemma-the-husky]

what could ANDALSO imply, that AND does not?

 

[Heatshiver]

AndAlso checks the first statement, and if that is already not true, skips the second. For some reason And isn't working for even though one part is false, so I had planned to use AndAlso.

 

[gemma-the-husky]

ah

in vba both parts of an and are executed

you need to do this to ensure that condition b is checked only if condition a is true

if condition a then
     if condition b then 
     end if
end if

you might be able to do it the other way, using negatives

if not(condition a) or not(condition b)

the second or will only evaluate if the first is true.

 

[Heatshiver]

I had tried this without success, but I'll try it again, may have been just too tired to realize. I may have to try it but switch the places of the conditions.

 

[Heatshiver]

Just tried it, and it still gives me an error on the first part (even switched).

Switched the first part from this:


If [Forms]![frmDates2].Enabled = False Then
If DLookup("Days", "tblDailyReport", "[Days] = #" & [End Date] & "#") Then
'Do Nothing
End If
Else
MsgBox "No report with that Start Date has been filled out."
Exit Sub
End If

To:

If Me.[End Date].Locked = False Then
If DLookup("Days", "tblDailyReport", "[Days] = #" & [End Date] & "#") Then
'Do Nothing
End If
Else
MsgBox "No report with that Start Date has been filled out."
Exit Sub
End If


And the second condition is still read, even though it should be skipped since the first condition is True.

 

[spikepl]

I would advise you to :

1. Decide whether you are checking on .Enabled or .Locked
2. Compile your code frist, because the output of Dlookup is not a Boolean, as your test implies, and the compilation should fail.

This is also one of the reasons why "I got an error" is an information-free filler, whereas "It gave error so and so" gives pointers as to what is wrong.

 

[VilaRestal]

DLookup("Days", "tblDailyReport", "[Days] = #" & [End Date] & "#") is always going to return [End Date] (whatever that is - Me![End Date] I guess it should be). And it should be compared to something. Auto-casting a date to boolean is a bit bizarre.

But anyway

Dim b As Boolean 
If (Condition A) Then b = (ConditonB)
If b Then
 
Else
    MsgBox "No report with that Start Date has been filled out."
    Exit Sub
End If

Is the way to do your 'AndAlso'.

I think what you want is:

Dim b As Boolean 
If Me.[End Date].Locked = False Then b = (DCount("*", "tblDailyReport", "[Days] = #" & Me![End Date] & "#") > 0)
If b Then
   'Do Nothing
Else
   MsgBox "No report with that Start Date has been filled out."
    Exit Sub
End If

 

[gemma-the-husky]

partial quote

DLookup("Days", "tblDailyReport", "[Days] = #" & [End Date] & "#") is always going to return [End Date] (whatever that is - Me![End Date] I guess it should be). And it should be compared to something. Auto-casting a date to boolean is a bit bizarre.

I do not think so. the dlookup will either return [end date] if it is found, or null if it is not found.

and also, if the OP is in the UK, the dlookup may not work correctly anyway, as [end date] may get treated as a US date, rather than a UK date.

I think maybe the OP ought to explain exactly what he is trying to do, though.

 

[Heatshiver]

@VilaRestal - I will try your suggestion tomorrow and get back on the results.

@gemma-the-husky - The DLookup did work as expected previously, but not in this situation.

I do use the US date, although I am currently overseas.

Here is a breakdown of what I am trying to accomplish:


1st Form - A main menu that leads to the 2nd form.

2nd Form - Gives a choice of forms or reports (this situation involves reports, 3rd form).

3rd Form - The type of report is chosen. The first choice uses the 4th form (date form) as it is with the DLookup as the only condition. It has a Start and End date for projects. It works perfectly. The second choice also uses the 4th form (date form) but locks off and disables the End Date. This is where this specific situation occurs.

4th Form - The date form. The user chooses their dates as necessary leading to the 5th form.

5th Form - Is the specific report needed and leads to a 6th form.

6th Form - Specific report.


The only way I have gotten this to work as ordered here is to have two 4th forms, one for the first choice of reports, and another for the second. I then take out any code referring to the End Date in the second form and it works just fine.

However, I would really like to figure out the problem here as I already have a lot of forms, and more to come. Having one would help in terms of redundancy and locating any future issues. I am hoping VilaRestal's solution works!

 

[gemma-the-husky]

why not use the "locked off form", but in code, set the startdate to be the same as the enddate.

then you don't end up having a special case

you can then always use the saem format - between startdate and endate - it's just that they will both be the same value

 

[Heatshiver]

I am trying to avoid using two forms. When I do, there isn't a problem.

 

[Pat Hartman]

If Me.[End Date].Locked = False Then
If DLookup("Days", "tblDailyReport", "[Days] = #" & [End Date] & "#") Then
'Do Nothing
End If
Else
MsgBox "No report with that Start Date has been filled out."
Exit Sub
End If

I think the test is backwards which is why you think it doesn't work. Shouldn't you be testing for Locked = True?
In the previous example where you were checking the Enabled property, you were not referencing a control, you were referencing a form which was why that didn't work.

 

[Heatshiver]

If it isn't locked then it should validate, not if it is locked. Otherwise it would be checking for a validation that will definitely get an error because it is locked.

I actually was checking a control, just was writing fast here and didn't catch that.

 

[Heatshiver]

@VilaRestal - Unfortunately your VBA didn't work either. Thanks for trying.

 

[VilaRestal]

Ah the infamous 'didn't work' error. How I hate that error.