Error 3464 data type mismatch in Visual Basic

[June7]

The apostrophe edits are where used to be # in:

If Me.cboCrit2 <> "" Then
          If strCrit = "" Then
                     strCrit = Me.cboCrit2.Tag & "='" & Me.cboCrit2 & "'"


Else


strCrit = strCrit & " AND " & Me.cboCrit2.Tag & "='" & Me.cboCrit2.Value & "'"


End If
End If

[russianthistle]

My apologies if this is frustrating. I took two access classes online and am following the instructor's lessons to try to create a usable database for my team at work. I have no other coding or VB experience, and I am trying to follow the advice given. My understanding is based on my limited exposure to the program. You are probably correct, and this may be an inefficient way to create the program. I am not sure why they decided to teach us the DLookup. Not knowing how to write SQL statements on the VB side, I just used the steps that were provided to me. If the college had a third access course, I would take it. I absolutely need more knowledge than what I currently have.. I am just trying to make it work.

Micron, I understood your advice to be that I needed to go in and replace the # with '. Within the program I used Ctrl+F to find the #, and replaced both of them with '. Scroll up to the last code I had posted to see that - or here is a screenshot with the ' highlighted:


Is that not what you wanted me to do?




I cleared out the data and have attached the database for further review. Hopefully that will help.
Rounding Database attempting to resolve issues.zip

Edited to add the reports that are not function -

Report ID 01, Control cboCrit2 (building)
Report ID 03, Control cboCrit1 (office name)
Report ID 03, Control cboCrit3 (trainer)

Some of the criteria are set up as numbers, so that I can let end users select an option off of a list in the forms. Would it work to move all of these to one criteria (cboCrit3 perhaps) and change the delimiter type for that one criteria?

[Micron]

I started a reply, must've lost it. I was going by a prior post (5?) rather than a later one where it was all corrected, so my mistake. I'll have to review this thread and take a look at your db. Maybe somebody will beat me to it

[Micron]

I may be wrong because it can be difficult to figure out what's going on in someone else's db in absence of detailed instruction, but my conclusion about your problem is thus:
- you have LOTS of lookup fields in your tables, which is something most (all?) experienced developers would never have. When you tell Access (query, report, form, etc.) to do something based on such a field, you're using the visible value from that field. Problem is, the value you see comes from a hidden table that cannot be uncovered, and while the value you see may be "ABCD" the value that is actually stored there is an index number from the hidden table. Therefore, what you really need to use as criteria (in a query) is that index number. Since you can't see it, the only way you can get it is by referring to its .Value property, which is not to be confused with the value property of a control such as a textbox. If you comment out this line and change it to

DoCmd.OpenReport Me.cboReport.Column(3), acViewPreview, , "PracticeName= 6"

it will run with no error, but I have no idea what a valid number would be because you're trying to apply a where condition, so the value isn't even available. You could try getting it as a separate DLookup I guess, but I've never tried that for obvious reasons. The sad news is, you should start over.

Refer to http://access.mvps.org/access/lookupfields.htm as to why you've probably set yourself up for more grief if you continue with these fields.

IMHO, the only justification for using such fields is to work directly in tables, and that's another no-no, so it's really not a valid reason at all. If I'm not mixing up posters, you've said this is based on some sort of school course. If that's where you learned to use table lookup fields, shame on them.


EDIT: played a little more. With report Training Log and Monroe Cancer Center, 18 works, but as I said, you're trying to filter according to "Monroe Cancer Center" . That test showed me that you're actually using the ID field of the supporting table MasterRoundingList and not a hidden table. I still maintain lookups are to be avoided, whatever they're based on.

[russianthistle]

Yes, I took two college level courses on Access to develop this, I went as far as even saving every individual lesson, screenshot, and quiz in order to reuse the steps. I spent countless hours trying to learn this first for the classes, and then later here at work to make something functional. Unfortunately the previous system used here at my work has been a patchwork of excel spreadsheets with no way to effectively update or run reports through. The data in that format has been basically unusable, and there is no other effort to improve the functionality. I'm not even on the technical end of my field, just the only one motivated enough to try to envision a better system. And of course, I am the only one who is even trying to use Access, which has left me with little options.

I'll dig in a bit deeper into your suggestion and see if I can make something work with what I have... Starting over is not really tempting, as I do not know how to do this any other way. Not knowing any VB and having what I now realize is very limited understanding of Access, trying again seems like it would just be a way to double my wasted efforts.

Edited to add, thank you for attempting to help Micron, I do appreciate you taking the time to do so. I work as a trainer for a program and can imagine how frustrating it must be having someone butcher the program and then ask for help fixing something unusable. It is really generous of you to spend your free time trying to assist.

[Micron]

trying again seems like it would just be a way to double my wasted efforts.

Believe me, I know the feeling. I built a db a few months ago to catalog slide (photographic transparencies) copying. Database correlates tray slot to subject details and slide number that was entered on form, matches camera copy ID to slide ID, reverses all images, rotates portrait images, matches thumbnail file# to slide# -a lot of code. I got about 1,000 copies into the project then realized I couldn't keep track of the copied ones against the ones not copied when they came from their original containers (I only have about 10 trays). Got all balled up and put it away for a month & just got back to it today. Am starting all over again.

The point is, this will be better than tearing my hair out while I try to fix what's mixed up and not make it worse in the process, and I think the same goes for you. Access is easy to do poorly, and the more research you do before hand will only help. I realize a course should be about the best chance you'd have, which is why it blows me away that you were taught to do things in such a manner. My advice would be to get off of the road you're on and take a new path after a break. In the meantime, start a Word document for thoughts, questions, links to pages, subjects of interest (especially those that are a bit too advanced at first) and make detailed comments and notes. If you're too cryptic you won't remember what you were thinking at the time. Don't accept the first method that you come across (that didn't work so well, did it?), but do feel free to try it for practice. With respect to concepts, search on the keywords and find other examples for comparison, but above all, get normalization right and start with some basics just to be sure you're up to speed. I have a set of links I recommend:

Normalization is paramount. Diagramming maybe not so much for some people.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
bookmark this site for sure http://allenbrowne.com/tips.html

Then there is post 5 here https://www.accessforums.net/showthread.php?t=75189
You can also find white papers and other such treatises; other people here frequently cite videos by Steve Bishop. Whichever method of presentation speaks to you is best for you. Also, we're here to answer questions should you start over. I guess if no one else is picking up the ball, it's your game on your time. I'm not sure just how far back you have to go; parts of this whole response may be too basic. However, one of them does say don't use lookup fields which wasn't too basic in your case.

I commend you for wanting to make things better and I know how that feels because my best example is a db that I created that took 4 hours of work and reduced it to about 6 minutes. I went in on overtime once to help a crew with a tedious documentation exercise and knew there was a better way and proved it. I hope you can stick with it and wish you luck.

P.S. - one of your best db design tools is pencil and paper. If you can't sketch it (tables and relationships), you won't be able to build it.