Access07 Syntax Errors

[KC_Cheer_Coach]

I am creating a database in Access 2007 and am having problems populating the
text fields with data from a table that is not the record source of the main
form. I have tried several variations of the following expressioins and they
all come back with invalid syntax errors.
First: I want the expression to pull the FacilityAmt from the
tblFacilityTypes table where the FacilityCode from that table equals a
certain type; in this case it is B03. I have used:
=DLookUp("[FacilityAmt]","tblFacilityTypes","[FacilityCode] = B03")
=DLookUp("[FacilityAmt]","tblFacilityTypes","[FacilityCode] = 'B03'")
=DLookUp("FacilityAmt","tblFacilityTypes","FacilityCode = 'B03'")
Second: I want to populate a Year field with the year based on the current
date. I know…seems redundant, but that is the requirement placed on me.
Currently the field is populated manually, but I should be able to use the
following to take away human error:
=DatePart(“yyyyâ€,Date())
Since I receive a syntax error on every function I type into the database
except for Date(), I thought it may be corrupt and I started over. I am
getting the same syntax errors.
Is there something wrong with the syntax? I have been all over the internet
and tried so many different ways to do this with no luck. I don't want to
hardcode this form if I can help it. OR…Is there another way other than using
expressions in the control source of the property sheet.
Thank you in advance!

 

[M Skabialka]

Format(Date, "yyyy")

Square brackets as in [FacilityCode] are not usually necessary unless there
is a space in the field name, e.g. [Facility Code], so in this case they
make no difference.
Have you tried putting this code into the Immediate window in your code
module?
Debug.Print DLookUp("FacilityAmt","tblFacilityTypes","FacilityCode = 'B03'")
Hit enter at the end of the line.
Do you get the correct result?
If so, is the field on your form the correct data type for FacilityAmt?
If not, what is the error message?

In the code window, under Tools, References, are there any listed as
Missing? Add them, then close and open Access.

 

[KC_Cheer_Coach]

The information you provided was helpful. I put the code in the Immediate
window and it confirmed that the syntax is correct and everything works as
intended. Am I just putting it in the wrong place?? Isn't this able to be
placed on the control source line in Access or do I need to place it in a
module?

Thanks!

Format(Date, "yyyy")

Square brackets as in [FacilityCode] are not usually necessary unless there
is a space in the field name, e.g. [Facility Code], so in this case they
make no difference.
Have you tried putting this code into the Immediate window in your code
module?
Debug.Print DLookUp("FacilityAmt","tblFacilityTypes","FacilityCode = 'B03'")
Hit enter at the end of the line.
Do you get the correct result?
If so, is the field on your form the correct data type for FacilityAmt?
If not, what is the error message?

In the code window, under Tools, References, are there any listed as
Missing? Add them, then close and open Access.

I am creating a database in Access 2007 and am having problems populating
the
text fields with data from a table that is not the record source of the
main
form. I have tried several variations of the following expressioins and
they
all come back with invalid syntax errors.
First: I want the expression to pull the FacilityAmt from the
tblFacilityTypes table where the FacilityCode from that table equals a
certain type; in this case it is B03. I have used:
=DLookUp("[FacilityAmt]","tblFacilityTypes","[FacilityCode] = B03")
=DLookUp("[FacilityAmt]","tblFacilityTypes","[FacilityCode] = 'B03'")
=DLookUp("FacilityAmt","tblFacilityTypes","FacilityCode = 'B03'")
Second: I want to populate a Year field with the year based on the current
date. I know.seems redundant, but that is the requirement placed on me.
Currently the field is populated manually, but I should be able to use the
following to take away human error:
=DatePart("yyyy",Date())
Since I receive a syntax error on every function I type into the database
except for Date(), I thought it may be corrupt and I started over. I am
getting the same syntax errors.
Is there something wrong with the syntax? I have been all over the
internet
and tried so many different ways to do this with no luck. I don't want to
hardcode this form if I can help it. OR.Is there another way other than
using
expressions in the control source of the property sheet.
Thank you in advance!

 

[KC_Cheer_Coach]

It is all working now when I put it in the control source. Thank you for your
help.

Format(Date, "yyyy")

Square brackets as in [FacilityCode] are not usually necessary unless there
is a space in the field name, e.g. [Facility Code], so in this case they
make no difference.
Have you tried putting this code into the Immediate window in your code
module?
Debug.Print DLookUp("FacilityAmt","tblFacilityTypes","FacilityCode = 'B03'")
Hit enter at the end of the line.
Do you get the correct result?
If so, is the field on your form the correct data type for FacilityAmt?
If not, what is the error message?

In the code window, under Tools, References, are there any listed as
Missing? Add them, then close and open Access.

I am creating a database in Access 2007 and am having problems populating
the
text fields with data from a table that is not the record source of the
main
form. I have tried several variations of the following expressioins and
they
all come back with invalid syntax errors.
First: I want the expression to pull the FacilityAmt from the
tblFacilityTypes table where the FacilityCode from that table equals a
certain type; in this case it is B03. I have used:
=DLookUp("[FacilityAmt]","tblFacilityTypes","[FacilityCode] = B03")
=DLookUp("[FacilityAmt]","tblFacilityTypes","[FacilityCode] = 'B03'")
=DLookUp("FacilityAmt","tblFacilityTypes","FacilityCode = 'B03'")
Second: I want to populate a Year field with the year based on the current
date. I know.seems redundant, but that is the requirement placed on me.
Currently the field is populated manually, but I should be able to use the
following to take away human error:
=DatePart("yyyy",Date())
Since I receive a syntax error on every function I type into the database
except for Date(), I thought it may be corrupt and I started over. I am
getting the same syntax errors.
Is there something wrong with the syntax? I have been all over the
internet
and tried so many different ways to do this with no luck. I don't want to
hardcode this form if I can help it. OR.Is there another way other than
using
expressions in the control source of the property sheet.
Thank you in advance!