I solved my own problem!
The answer is that the Year function was causing the problem. To get around this issue, I created an invisible unbound text box in the form that got the year from the date field. Everything else stayed the same except removing the year function from the qdf.paramters(1 ) line and reassigning it to my new invisible text box.
birdDBheadache
Last Activity: Mar 25 '14, 02:31 PM
Joined: Mar 13 '14
Location:
-
-
Using a query with a form reference in DAO
I am trying to use a query with a form reference in DAO, but it does not return any records. Here is my code:
...Code:Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim rs1 As DAO.Recordset Set db = CurrentDb() Set qdf = db.QueryDefs("qry_Species_by_park_by_year") qdf.Parameters(0) = Forms!Occu_formatting!Park qdf.Parameters(1) = Year(Forms!Occu_formatting!survey_begin_date) -
That worked! Thanks! I was even able to use my existing form with only minor tweaking.Leave a comment:
-
I don't have much VBA background, but I can do fairly basic things. I like the idea of using a new form, but I am flexible. If you can show me an example of how to step through each record and pass it to the query, I think I can pretty easily apply it to my existing database.
Thanks for your help.Leave a comment:
-
Yes. That is correct. All the data for Value_A are already in the database. Right now I just query it and then enter it manually (which seems like a waste of time). So stepping through each value is exactly what I need to do.Leave a comment:
-
Passing Query Result to a Form or List
I have a form in MS Access where I enter a value (I'll call it Value_A) that is then passed to multiple queries. After the queries run, the results of the final query are exported to a .csv file for import into another program.
To summarize my workflow: 1) I enter a Value_A into a field in Form1, 2) I click a button in Form1 that initiates an event where Value_A is passed to a series of queries (using an On Click Event sub procedure... -
I am interested in learning more about using normalized databases, so I appreciate all the information. I have made them in the past, but it has been several years. I think I will see this current project to the end and then rethink the approach. All of the data are entered so, the database won't be getting any larger for another year.
Thanks again!Leave a comment:
-
I think normalizing would solve many of my problems I am having, and many of my previous problems, but I don't have that much experience with using normalized dbases and my collaborators like to be able to look at all my tables as if they are looking at a spreadsheet.
Thanks for all your help.Leave a comment:
-
IT WORKED! But it starts counting at zero instead of 1. This isn't a huge deal, but is there a quick fix?
...i should have noticed the open parenthesis...i have been staring at the screen too longLeave a comment:
-
I am getting the error 'Missing operator in query or expression'
...Code:SELECT (ID , Name_ID , Survey_Point , Survey_date , DCount("*", "tbl_survey_details" , "Name_ID='" & Name_ID & "'" & " And Survey_Point=" & Survey_Point & " And Year(Survey_date) =" & Year(Survey_date) & "Leave a comment:
-
Ok. I think Dcount is the right direction I should go. I've attached a reallllly simplified screen shot version of what the data look like. If I can see an example implemented in this, I think I can apply it to the real thing.
Right now, the problem I am having is that it seems to be returning a full count of all the records for some reason, not just returning an incremented value....Leave a comment:
-
Update Query Counter
I am trying to write an update query in MS Access to update a count field in my database. I have a table that has records with multiple fields for every time a surveyor goes to a particular survey point. The surveyor enters information into the database to populate the fields including the point number([Survey_point]), the date([Date_surveyed]), time, etc. and usually goes to point multiple times in a year (but not always).
I would like... -
Ok that worked...now on to the next problem!Leave a comment:
-
Passing a string to an expression in Access
I am trying to take the results of a subroutine (which is the string strFields in the code below) and pass them to an expression in an Access query. Is this even possible or am I thinking about this the wrong way? Can I only use functions in an expression?
...Code:Sub Fieldnames() Dim Rst As Recordset Dim strFields As String Dim db As Database Dim f As Field Dim qdfParmQry As QueryDef -
That worked, thanks!Leave a comment:
-
Help Writing Field Names to a String
I am trying to concatenate field names (separated by a comma or some of the delimiter) and write them to record in my database, or store them as a string that I can use in an expression later on. So far I have code that pulls all the field names from a query and writes them to the debug window. But I am stuck after that. I can't figure out how to get the output into another format. Any suggestions would be appreciated.
...Code:Sub
Last edited by Rabbit; Mar 13 '14, 07:17 PM. Reason: Please use [code] and [/code] tags when posting code or formatted data.
No activity results to display
Leave a comment: