I know that this thread is a couple of days old, but I don't see why you would have to go to the trouble of creating a dynamic SQL statement. You could simply go into your parameter query and put the criteria you want to "hard code" into the appropriate fields. Users would still be prompted...
You can insert a field in the underlying table that has a data type of hyperlink. Then each entry would have its own specific link, and clicking on it would automatically open the file.
I would love to be able to send the report as an email, but they use Lotus Notes here, and the code that I have found on this site and others doesn't seem to support that.
I can live with having to export the spreadsheet, but I would just like to be able to turn off those Excel errors. I am...
At the risk of sounding simplistic, can't you use the sorting and grouping tool? I also have a report that is generated by an SQL statement, and I put an ascending sort on one of the fields using that dialog box and it seemed to work.
First of all, I just want to make sure...it's *not* possible to simply copy and paste a report or parts of a report into, say, Lotus Notes (unless you want to do a screen print), right?
Assuming that it's not possible to do that, I am wondering if there is a better way of exporting a report to...
You got me to thinking....I typed in
=Right([Medrec],3)
under Field/Expression in the grouping and sorting dialog box, and it seems to have worked. I guess I didn't realize you could do that!
Thanks for putting me on the right track!
Is it possible to sort a report on part of a field, such as the last three numbers of a medical record #? Can I enter some sort of expression in the sorting and grouping dialog box that will do this?
TIA
If you want to be able to enter criteria into a form, you could have an unbound form (not based on a particular table or query) where you enter criteria and then have a query that pulls the input from those comboboxes to use as criteria for the query. You can reference a particular combo/text...
If you want to create the "unmatched" query Pat suggested, on the queries tab in your db click on the New button on the right hand side and then choose the Find Unmatched Query Wizard. It's pretty self-explanatory...
The easiest thing to do would be to use the wizard to create the combo box and choose the third option, Find a record on my form based on the value I selected in my combo box.
I still can't get the form to open up with a specific date as a criteria. I will keep plugging away at it, but luckily the Powers That Be have now decided that the date field is not a necessary search criteria, so I am off the hook so to speak. Still, it drives me crazy when I can't get...
Thanks for sticking with me, Jack!
I *thought* I was putting the where clause in the right place. I mean, it works correctly for every other unbound field, so why does this one field not work?
Also, if I try to put in this
DoCmd.OpenForm "frmADR", , , mySql
with three commas like you have...
I thought that I had to use mySql to insert the "WHERE" in my SQL statement? When I use myWhere instead of mySQL, I get ALL records in the database instead of just those with the specified criteria.
I just keep thinking this has to be something with the syntax for date, because I have 16...
I copied your suggestion, Jack, and now I'm not getting that error, but I am not getting the correct records, either. I put a msgbox in to show what the value of mysql is right before I open the form, and now it says
Select * from tblADR where False
instead of where letterdate = 5/4/01 etc...
Thanks for the suggestion, Jack. Maybe I am reading your reply wrong, but when I tried that, I got a run-time error 2501 saying the OpenForm action was cancelled.
Here is what I have:
myWhere = myWhere & " ([letterdate] like '" & Forms!frmsearchadr!txtadrdate & "')"
Do I have my single and...
This is stumping me, although it must be something obvious.
I have an unbound form with a text box for users to enter a date. I want to take that date and generate an SQL statement that will open up my main form showing entries with the specified date. Here is what I have, but it will not...
You could put something in the BeforeUpdate event of the form that would check for the blank field and then produce a msgbox. Something like
If IsNull(me.yourtxtboxname)or Len(yourtxtboxname)=0 Then
Msgbox "Enter a date!"
Else....
End if
Check out this thread....
http://www.access-programmers.co.uk/ubb/Forum4/HTML/004007.html
I was able to adapt this to my needs. You also might look into using Querydef. Here is the Knowledgebase article on it.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q136062
Sometimes I think it is easier to use the Mail Merge function in Word...you can choose a datasource (like Access) and get a much more customized look for labels.