Using Month() in If Then Statement

rachelkm2

Registered User.
Local time
Today, 14:30
Joined
May 29, 2009
Messages
24
I'm trying to create an If Then Statement using criteria based on a calculated Month field. My code currently looks like:

If [tbl_ImportedMonths].[ImportMonth] = Month([tbl_Time].[Time.Date]) Then
MsgBox "The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported
End If
If MsgBox("The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported) = vbNo Then
GoTo exit_Sub
End If

I cannot for the life of me get the syntax right. Anything anyone can do to help would be much appreciated!
 
Try;
Code:
If [tbl_ImportedMonths].[ImportMonth] = Month([tbl_Time].[Time.Date]) Then
     If MsgBox ("The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported) = vbNo Then
      GoTo exit_Sub
End If
 
Thanks for your quick response! I tried that, but then I get:

The expression On Click you entered as the event property setting produced the following error: Block If without End If

I think the issue is with using the Month() function. I had similar syntax issues elsewhere, but finally got the following example to work:

myUpdateYesNoSQL = "UPDATE tbl_Time, tbl_ImportedMonths"
myUpdateYesNoSQL = myUpdateYesNoSQL & " SET tbl_Time.AlreadyImported = True"
myUpdateYesNoSQL = myUpdateYesNoSQL & " WHERE (((Month([tbl_Time].[Time_Date])) Like ([tbl_ImportedMonths].[ImportMonth]))"
myUpdateYesNoSQL = myUpdateYesNoSQL & " AND ((Year([tbl_Time].[Time_Date])) Like ([tbl_ImportedMonths].[ImportYear])))"
DoCmd.RunSQL myUpdateYesNoSQL

I'm trying to use a similar calcualtion with my If Then statement, but it doesn't seem to be working.
 
How are you trying to use this. Referring directly to a field in a table is not the go.

BTW [Time.Date] is not a suitable name for a field. Any special characters should be avoided in object and field names but the dot is about the last thing you would want.
 
How are you trying to use this. Referring directly to a field in a table is not the go.

BTW [Time.Date] is not a suitable name for a field. Any special characters should be avoided in object and field names but the dot is about the last thing you would want.

Thank you! That was definitely part of the problem - my field name is not [Time.Date] it's actually [Time_Date] :o

With that piece solved, can you point me in a better direction towards getting this to work if not by referring directly to a field in a table?

I'm trying to notify the user that the month they have imported ([ImportMonth]) which is an integer, already exists in [tbl_Time].[Time_Date] which is a mm/dd/yyyy date.

Thank you.
 
Sorry forgot to close one of the If's :o, try;
Code:
If [tbl_ImportedMonths].[ImportMonth] = Month([tbl_Time].[Time.Date]) Then
     If MsgBox ("The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported) = vbNo Then
        GoTo exit_Sub
     End If
End If
 
Sorry forgot to close one of the If's :o, try;
Code:
If [tbl_ImportedMonths].[ImportMonth] = Month([tbl_Time].[Time.Date]) Then
     If MsgBox ("The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported) = vbNo Then
        GoTo exit_Sub
     End If
End If

Thanks, that helps with the If Then error message I was getting. But now I get:

Run-time error '2465': Microsoft Access can't field the field '|1' referred to in your expression.

I definitely think my issue is with the syntax in the first line.
 
Code:
myUpdateYesNoSQL = "UPDATE [B]tbl_Time, tbl_ImportedMonths[/B]"
myUpdateYesNoSQL = myUpdateYesNoSQL & " SET tbl_Time.AlreadyImported = True"
myUpdateYesNoSQL = myUpdateYesNoSQL & " WHERE (((Month([tbl_Time].[Time_Date])) Like ([tbl_ImportedMonths].[ImportMonth]))"
myUpdateYesNoSQL = myUpdateYesNoSQL & " AND ((Year([tbl_Time].[Time_Date])) Like ([tbl_ImportedMonths].[ImportYear])))"

You have two table here that are not joined.

Also you do not need to use Like

Code:
" WHERE Month([tbl_Time].[Time_Date]) ='" & [tbl_ImportedMonths].[ImportMonth] & "'"

If you do a join between the two then you should be ok.
 
Try;
Code:
If [B][COLOR="Red"]Me[/COLOR][/B].[ImportMonth] = Month([B][COLOR="Red"]Me[/COLOR][/B].[Time.Date]) Then
     If MsgBox ("The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported) = vbNo Then
        GoTo exit_Sub
     End If
End If

Which assumes that both those fields appear on your form.
 
Code:
myUpdateYesNoSQL = "UPDATE [B]tbl_Time, tbl_ImportedMonths[/B]"
myUpdateYesNoSQL = myUpdateYesNoSQL & " SET tbl_Time.AlreadyImported = True"
myUpdateYesNoSQL = myUpdateYesNoSQL & " WHERE (((Month([tbl_Time].[Time_Date])) Like ([tbl_ImportedMonths].[ImportMonth]))"
myUpdateYesNoSQL = myUpdateYesNoSQL & " AND ((Year([tbl_Time].[Time_Date])) Like ([tbl_ImportedMonths].[ImportYear])))"

You have two table here that are not joined.

Also you do not need to use Like

Code:
" WHERE Month([tbl_Time].[Time_Date]) ='" & [tbl_ImportedMonths].[ImportMonth] & "'"

If you do a join between the two then you should be ok.

Thanks. The code I have above actually works, I was just using it as an example of what I'm trying to do. It's the following code (first line) that I'm having trouble with (sorry, can't figure out how to post this as code) :


If [tbl_ImportedMonths].[ImportMonth] = Month([tbl_Time].[Time_Date]) Then
If MsgBox("The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported) = vbNo Then
GoTo exit_Sub
End If
End If
 
Try;
Code:
If [B][COLOR="Red"]Me[/COLOR][/B].[ImportMonth] = Month([B][COLOR="Red"]Me[/COLOR][/B].[Time.Date]) Then
     If MsgBox ("The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported) = vbNo Then
        GoTo exit_Sub
     End If
End If

Which assumes that both those fields appear on your form.

They don't appear on my form. My form simply contains one button. The user is asked to select a file to import (from a file dialog) and then the file is imported and appended to various tables. I'm trying to figure out a way to notify the user that they have already imported the month they are selecting, and give them the option to replace the existing data or cancel the import.
 
Thanks to everyone for their help! Finally got it to work with the following:

Code:
Dim myImportMonth As String
Dim myExistingMonth As String

myImportMonth = "[tbl_ImportMonths].[ImportMonth]"
myExistingMonth = "Month([tbl_Time].[Time_Date])"
 
If myImportMonth = myExistingMonth Then
    MsgBox "The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported
End If
If MsgBox("The month you have chosen already exists. Continue?", vbYesNo, MonthAlreadyImported) = vbNo Then
    GoTo exit_Sub
End If
 
I'm sorry but I fail to see how that is going to work :confused:

You are comparing two string expressions, myImportMonth and myExistingMonth one contains the literal string [tbl_ImportMonths].[ImportMonth] and the other Month([tbl_Time].[Time_Date]), Notice that in your code these two expressions are enclosed in double quotes so are simply treated as strings in their own right, they are never ever going to be equal. The only reason this code may appear to be working is because unlike the code I suggested you have un-nested the second If Then statement.

I think I mentioned previously that you will need to use the Dlookup() function to get some actual values into your variables.
 
I'm sorry but I fail to see how that is going to work :confused:

You are comparing two string expressions, myImportMonth and myExistingMonth one contains the literal string [tbl_ImportMonths].[ImportMonth] and the other Month([tbl_Time].[Time_Date]), Notice that in your code these two expressions are enclosed in double quotes so are simply treated as strings in their own right, they are never ever going to be equal. The only reason this code may appear to be working is because unlike the code I suggested you have un-nested the second If Then statement.

I think I mentioned previously that you will need to use the Dlookup() function to get some actual values into your variables.

You're right, it only looked like it was working:)

I haven't used Dlookup, so I'm not entirely clear how to use it, but I appreciate the point in the right direction - thank you!
 

Users who are viewing this thread

Back
Top Bottom