I have two tables a database that I am trying to run a command so that I can filter records that I am viewing. I have always used Visual dBase is the past...in that program I would write an index and use this as criteria:
at(substr(sc,1,2),'30,40,45,51,59,66,67')<>0
This takes the field "SC" and looks at only the first 2 characters of the fields and returns the numbers that I selected,
In Access I received some help also but I just cannot get over this part. This is what I used:
Field: NEWFLD:[PERMIT]![Left("field1",2)]
Table:
Sort:
Show: X
Criteria: In (40,45,66,67)
OR:
In the criteria field I also tried: In (40) and 40 by itself. I am just not getting anywhere. I know I have to be close but still need a little help. With the above entered in the query window, when I switch over to Datasheet View I get the following pop-up Window...
Enter Parameter Value (title-bar)
PERMIT!Left("field1",2)
(empty text box)
OK and Cancel Buttons
No matter what I enter into the text box I don't get any results. I was hoping that maybe you could give me some direction. Like I said I had a little help in another forum but I just cant get to where I need to be. Any help you can offer I would greatly appreciate it!
If the value returned from your Left is a text then you will need to place " " around your 40 e.g. "40" I tried your LEFT on a number field and used IN(23,30) wth the correct results displayed.
The way you originally coded this, you had the Left... all inside the square brackets []. Square brackets tells Access, "This is the name of the field." That is why you were asked by Access to supply the missing parameter, because there is no field within PERMIT that is named Left("field1",2). All of those characters Left("field1",2) were assigned as the name of a field, but because there is no field with that name in the table named PERMIT, Access considers that a parameter within the query, and without a value for that parameter, will ask the user for the value.
FYI: Do not put a field name inside of quotes either. If this had been written in such a way that Access was not looking for a missing parameter, you would have gotten "fi" as the answer to the Left("field1",2).
The way I showed you to code this, the actual field name within the PERMIT table is the field that the Left function will act on for the first two characters in that field.
I have found I have a big problem before I can even start with the above. I'm using a dBase IV (Visual File). I thought it was being converted to Access 7 when I loaded it in to Access, how dumb can I be. No I don't call DOS Doous as we used to gauge a newbi back in the day.
But... How can I convert the dBase IV file to Access 7? I don't need to use the .mdx or .dbt file if that's an issue.
I believe you can link to the tables from a dBase database from within Access. Then if you want that data inside Access rather than inside dBase, the do a MakeTable query from the dBase linked table into an Access table (which the Make Table query will make for you).
Reply With Quote