Hi
Hope someone can help
I have a query that is trying to match two tables, one in MSSQL2000 and the other in Access 2003. The matching field in MSSQL is a Text field and in Access the field is a long integer. When I try to run the query it gives a Type Mismatch error.
I've tried all the functions that I can see to convert either of the fields data types but to no avail.
Does anyone know of a way to force a match?
Thanks
Mark
I am trying to do a basic query and I keep getting a "Type Mismatch" error and the query will not run. If I only do a query on one table, it works no problem so I know it must be related to my Join between tables.
For the two tables that are joined (it is one-to-many)- the first table is a clients table and I created a field called ClientNumber that is an AutoNumber. The second table is called TrainingRequests. This will store the training requests for each client and each client can have multiple training requests. I created a field called ClientNumber in it as well (this is what field I linked the tables by). But I set it to text instead of AutoNumber.
Is there a way to do a query with the two tables? Or will I have to change something in table design? I already have some data in the tables so I am not sure what direction to take.
Thanks for any help someone can provide. It would be greatly appreciated.
Query1:
Src: Table1 joined Table2
ID (Type Text)
Title (Type Text)
Remarks(Type Text)
Formatted: FormatTitle([title],[Remarks])
Expr1: InStrRev([Formatted], "~")
public functionFormatTitle(ByVal sTitle as String, ByVal sRemarks as String) as String
'do process code here very complicated an long, but works find in the end
'creates a Multi-String delimited by | (pipe)
end function
The above works, and Expr1 does give an accurate value for the position of a "~" (tilde) in the string Created by the FormatTitle() function.
However, If I put a Criteria >0 on Expr1 it asks for the value of the [Formatted] field as if it was a parameter. If I put a criteria for Formatted: Like "*~*" I get a Data Type Mismatch in Query Criteria
Query2:
Src: Query1
Title (Type Text)
Remarks (Type Text)
Formatted(Type Text)
Exr1 (Type Number) criteria >0
This Query Also produces the Data Type Mismatch in Query Criteria
pardon me, but WTF? If it isn't a STring, than InStrRev() should produce an error, not an accurate response, and if InStrRev() produces a number why can't i compare it to 0 (zero)? This is indubitably messed up that I'm getting this error. There is no data type mismatch, on either of these tests, one is a string and I criteria-limit it by a string operation, the other is a number and I criteria limit it by a number, WHAT IS GOING ON!!!
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner - The Frustratedly Confused
Working with two tables - tbl_A_OrdData & tbl_B_ShipData tbl_A have all the orders placed, but tbl_B only have the orders that got shipped. I'm trying to create a query that will show me all orders that got placed for a particular criteria and if the order find a match in tbl_B to give me the ship date too. A left joint query get me the data I need. But my dates are in YYYYMMDD number format so in the query I am putting the DateValue function. And that error out as "data type mismatch" because it cannot find the shipdate for certain records. I tried this but didnt work. ShipDt: IIf (IsNull ([PSHPDT]), "", DateValue(Mid([PSHPDT],5,2) & "/" & Right([PSHPDT],2) & "/" & Left([PSHPDT],4)))
View 2 Replies View RelatedI have vba code that creates the following SQL:
SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1
FROM [qryScheduleCombinedDetails]
WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);
When I try to run it, I get a "data type mismatch" error. When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.
WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.
I'm receiving an error indicating there is a data type mismatch when running a query named qappInventoryTakeOn.
Data is entered into the Inventory Transaction Form. If the transaction type is "Take On", when the update button is clicked the record will be saved to tblInventoryMovements and then qappInventoryTakeOn should run to update tblInventory, but I keep running into the aforementioned error.
I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.
Here's what it looks like in design view:
Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));
I get the error: data type mismatch in criteria expression when I run it.
I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.
The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.
The formula is
Code:
CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))
Hi
I posted this on the end of the audit trail thread but nobody was really looking at it. I have used the script for the audit trail, and when I start changing any of the records, it comes up with the error:
13 - Type Mismatch
When I ctrl - break, and debug, it highlights the last line of the script:
On Error GoTo Form_BeforeUpdate_Err
Call Audit_Trail(Me)
Form_BeforeUpdate_Exit:
Exit Sub
Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
Anyone any ideas on what is happening?
Hi all,
I have a problem with the following piece of code:
Private Sub cmdCautare_Click()
Dim strSQL As String, strOrder As String, strWhere As String
'Select Case Me.cmbTipVersus
'Case 1
'If Me.cmbTipVersus = "" Then
strSQL = "SELECT tblDosare.DosarID, tblDosare.DenumireDosar, tblDosare.CodDosar, tblDosare.DataDosar, tblInstante.Localitate, tblStadiu.Data, tblStadiu.Stadiu FROM tblInstante INNER JOIN (tblDosare LEFT JOIN tblStadiu ON tblDosare.DosarID = tblStadiu.Dosar) ON tblInstante.InstantaID = tblDosare.Instanta"
strWhere = "WHERE"
strOrder = "ORDER BY DosarID"
If IsNull(Me.txtDenumire) Or Me.txtDenumire = "" Then
Else
strWhere = strWhere & "(DenumireDosar) Like '*" & Me.txtDenumire & "*'"
End If
If IsNull(Me.cmbStadiu) Or Me.cmbStadiu = "" Then
Else
strWhere = strWhere & "(DenumireDosar) Like '" & Me.txtDenumire & "*' & " And " & (Stadiu) Like '" & Me.cmbStadiu & "*'"
End If
DoCmd.OpenForm "frmRezultateCautare", acNormal
Forms!frmRezultateCautare!lstRezultate.RowSource = strSQL & " " & strWhere & "" & strOrder
'End If
'End Select
End Sub
And on this line strWhere = strWhere & "(DenumireDosar) Like '" & Me.txtDenumire & "*' & " And " & (Stadiu) Like '" & Me.cmbStadiu & "*'"
i have an error message type mismatch. If i do the search only with the first text box the code works hjust fine but if i make the search w.r. with the second control (combo box cmbStadiu) the error appears. If anyone can help i will appreciate it.
Thank you all!
I am persistently getting the "Error Number:13; Type Mismatch" with the code below.
Any help is greatly appreciated...
BTW, in the SQL string, i will like to have the wildcard as "*" but the VB Editor automatically changes it to " * "... can that be a problem??
Code:
Dim db As DAO.database
Dim qdf As DAO.QueryDef
Dim sqlsearch As String
Set db = CurrentDb
If Not QueryExists("Q_Paint") Then
Set qdf = db.CreateQueryDef("Q_Paint")
Else
Set qdf = db.QueryDefs("Q_Paint")
End If
sqlsearch = "SELECT M_Paint.* From M_Paint " & _
"WHERE M_Paint.Color_Number = '" * " & "Me.txb_pcn_colornumber" & " * "';"
qdf.SQL = sqlsearch
DoCmd.OpenForm "F_Display_Paint"
DoCmd.Close acForm, "F_Paint_Catcodesearch"
DoCmd.Close acForm, "F_Search_Paint"
WHy whould this be a type mismatch?
Dim strLABCHECK As Integer
strLABCHECK = CInt(StrComp(strSymbol, "(LAB)"))
If (Len(strSymbol) < 6 & Len(strSymbol) > 1 & strLABCHECK < 0) Then
...
End if
This is an Access 2000 question.
Now, I know what the error message is supposed to mean, despite my total newbie-ness. My table Product has p/k ProdID (autonumber, long integer) and my table Items has ProdID (number, long integer) as a f/k. It's a one-to-many relationship (each Item is only one kind of Product, but each Product may have multiple Items.) But when I try to run a query in Access, I get the "Type mismatch in expression" error. The only relationship is between a number and an autonumber as described above.
Does anyone know what might be going on?
hey,
can someone pls tell me why i get an Type mismatch in expression err with this code:
INSERT INTO tblACAD ( PROIZVAJALEC, TIP, DN, KOSOV )
SELECT tblsifrant.Proizvajalec, tblsifrant.Tip, tblsifrant.DN, tblsifrant.Kosov
FROM tblsifrant, tblACAD
WHERE (((tblsifrant.Sifra)=[tblACAD.TIP]) AND ((tblsifrant.DN)=[tblACAD.DN]) AND ((tblACAD.KOSOV)>0));
If i remove one criteria to get this code then there are no err, but thats not what i want:
INSERT INTO tblACAD ( PROIZVAJALEC, TIP, DN, KOSOV )
SELECT tblsifrant.Proizvajalec, tblsifrant.Tip, tblsifrant.DN, tblsifrant.Kosov
FROM tblsifrant, tblACAD
WHERE (((tblsifrant.Sifra)=[tblACAD.TIP]) AND ((tblACAD.KOSOV)>0));
I create this code in design view so all the names and tables are correct.
So does anyone have any ideas?
Thx
Hi all,
I'm using DB to run a small poker league, here's my setup.
Tables:
players - player_id (autonumber), player_name (text)
tourn - tourn_id (autonumber), venue (text), entry_fee (number, currency), tourn_date (date/time, medium date), n_entrants (number, integer<>0)
status - status_id (autonumber), player_name (from players table), tourn_id (from tourn table), position (number, integer<>0), winnings (number, currency)
I'm trying to create a query, that groups by player name, the count of tourn_id, victories (count of position=1), cash_prizes (count of winnings>0), sum of winnings, sum of entry_fee, net winnings (sum of winnings - sum of entry_fee)
With the help of someone else, I get this, but I keep getting error msg "type mismatch in expression"
SELECT P.player_name, Count(T.tourn_id) AS CountOftourn_id, Sum(S.winnings) AS SumOfwinnings, Sum(T.entry_fee) AS SumOfentry_fee, (SELECT Count(position) FROM status WHERE position =1
AND status.player_name= P.Player_Name) AS victories, (SELECT Count(winnings) FROM status WHERE winnings>0
AND status.player_name= P.Player_Name) AS Cash_prizes
FROM tourn AS T INNER JOIN (players AS P INNER JOIN status AS S ON P.player_name=S.player_name) ON T.tourn_id=S.tourn_id
GROUP BY P.player_name;
What's wrong with the above?
Thanks in advance
Raju
Ive been working at this problem for a while now and I can't figure it out. I have a table full of Work Orders, and on the form for inputing the Work Order data, there is a field for the date that the WO was received, and the date it is wanted. There is also a field that tells if this was a rush job (Yes/No) field.
I need to make a query that will grab all the rush jobs, and sort by how many days were between the date received and date wanted. This would be easy, just subtracting the dates, but I need to have it not include weekends and holidays. I found an algorithm to do that and it seems to work well. However, when I run the query, it gives me a "Data type mismatch in criteria expression." error. At this point, to debug it, I put in a message box to see if it processes any entries. It does process them one at a time, but once it gets to a certain point I get that error. I have checked all the dates, making sure they are all valid and they are. I'm just really stumped here. Here is the code for my query:
SELECT [Usable Orders].[AI Number], [Usable Orders].[District Name], [Usable Orders].[CCM Name], [Usable Orders].[8255 Received], [Usable Orders].[Date Wanted], [Usable Orders].Rush, WorkingDays2([8255 Received],[Date Wanted],[AI Number]) AS [Working Days]
FROM [Usable Orders]
GROUP BY [Usable Orders].[AI Number], [Usable Orders].[District Name], [Usable Orders].[CCM Name], [Usable Orders].[8255 Received], [Usable Orders].[Date Wanted], [Usable Orders].Rush
HAVING ((([Usable Orders].Rush)="Yes") AND ((WorkingDays2([8255 Received],[Date Wanted],[AI Number]))<=10));
and here is the code for my WorkingDays2 (counts all working days excluding holidays) the AINumber field I put in for debugging purposes to see which entries were being processed.
Public Function WorkingDays2(StartDate As Date, EndDate As Date, AINumber As String) As Integer
'................................................. ...................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'................................................. ...................
On Error GoTo Err_WorkingDays2
Dim intCount As Integer
Dim rs As Object
Dim stSql As String
Dim con As Object
Dim Message As String
If StartDate > EndDate Then
MsgBox "Start Date is After End Date"
WorkingDays2 = -1
GoTo Exit_WorkingDays2
End If
stSql = "SELECT [HolidayDate] FROM tblHolidays"
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
'MsgBox "Today is " & Weekday(StartDate) & ", " & StartDate & "Opening AI Number is " & AINumber
Do While StartDate <= EndDate
rs.Find "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rs.EOF Then
intCount = intCount + 1
' Message = Message & " and today is a work day."
End If
Else
' Message = Message & "and today is not a work day."
End If
' MsgBox Message
'rs.MoveNext
StartDate = StartDate + 1
Loop
'MsgBox "Closing AI Number " & AINumber & "Count is " & intCount
'MsgBox "After Loop count is " & intCount & " and today is " & StartDate
WorkingDays2 = intCount
'MsgBox "Date is " & StartDate & "AI Number is " & AINumber & " and there were " & intCount & " days."
Exit_WorkingDays2:
Exit Function
Err_WorkingDays2:
Select Case Err
Case Else
MsgBox Err.Description & "Start date is " & StartDate & "End Date is " & EndDate
Resume Exit_WorkingDays2
End Select
End Function
I don't understand why this won't work.
I have a date field in a table. The default value for this field is Date().
The data type is Date/Time
The field is called Import Date.
Here is what I am trying to do in a query:
Month Imported: DatePart('m', 'Import Date')
I am getting a data type mismatch, and I don't see how.
If I put this:
Month Imported: DatePart('m', Date())
It works fine.
I am querying results using Access 97(Yes its old and no my company is not willing to upgrade.....:confused:). Anyhow, I am connecting to a corporate database that has two tables I am querying that should be joined on one field (wmg.wmgtk01_tnk.sys_i = wmg.wmgwo04_halfleg_actl.tnk_sys_i). I am running into a problem with these fields because they have been defined differently by my IT department. The "sys_i" field is a NUMBER of length 5, while the "tnk_sys_i" field is a NUMBER of length 20. Is there a way that I can trick Access into joining these fields even though they do not match? I am currently getting a "Data Type Mismatch" error when I attempt to create this join.
Thanks in advance for your help!
Hi,
I'm using the function found here http://support.microsoft.com/?kbid=210236to to repeat the values from the previous record automatically.
It works fine for one single form but I keep getting "type mismatch" when applying it to a subform within another subform within a main form.
Any ideas?
Thanks, in advance, for any help offered.
Tony
Hi I'm doing some work on an open source intranet for a friend and I've come unstuck with data types I guess.
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'str'
D:SITESEXTRANETCLIENTS../includes/connection_open.asp, line 104
I added a date field and now get this error.
The process is as follows:
add user - client-add.asp
client-add-processor.asp (& siteSQL.asp include)
the error occours on submiting the original form page
new feilds:
Code:<form method="post" action="client-add-processor.asp" name="strForm" id="strForm">..............edited...............<tr><td><b class="bolddark"><%=dictLanguage("HostingPeriod")%>:</b></td><td><input name="HostingPeriod" size="20" value="<%=Session("HostingPeriod")%>" class="formStyleShort" onkeypress="txtDate_onKeypress();" maxlength="10"><a href="javascript:doNothing()" onclick="openCalendar('<%=server.urlencode(date())%>','Date_Change','HostingPeriod',150,300)"><img border="0" src="<%=gsSiteRoot%>images/calendaricon.jpg" onmouseover="this.style.cursor='hand'" WIDTH="16" HEIGHT="15"></a></td></tr><tr><td><b class="bolddark"><%=dictLanguage("DomainPeriod")%>:</b></td><td><input name="DomainPeriod" size="20" value="<%=Session("DomainPeriod")%>" class="formStyleShort" onkeypress="txtDate_onKeypress();" maxlength="10"><a href="javascript:doNothing()" onclick="openCalendar('<%=server.urlencode(date())%>','Date_Change','DomainPeriod',150,300)"><img border="0" src="<%=gsSiteRoot%>images/calendaricon.jpg" onmouseover="this.style.cursor='hand'" WIDTH="16" HEIGHT="15"></a></td></tr>
To client-add-processor.asp
Code:sql = sql_UpdateClient( _session("Name"), _session("Rep"), _session("Client_Since"), _session("Standard_Rate"), _session("Address1"), _session("Address2"), _session("City_State_Zip"), _session("LiveSite_URL"), _session("DevSite_URL"), _session("Contact_Name"), _session("Contact_Phone"), _session("Contact_Email"), _session("Contact2_Name"), _session("Contact2_Phone"), _session("Contact2_Email"), _Active, _session("client_id"), _session("ClientFolder"), _session("HostingPeriod"), _session("DomainPeriod"), _session("Notes"), _session("WebFolder"))'response.write(sql)Call DoSQL(sql)%><!--#include file="../includes/main_page_open.asp"-->
includes/main_page_open.asp
Code:Function MediumDate(str) Dim aDay Dim aMonth Dim aYear aDay = Day(str) -line 104 'aMonth = Monthname(Month(str),TRUE) aMonth = Month(str) Select Case aMonthCase 1aMonth = "Jan"Case 2aMonth = "Feb"Case 3aMonth = "Mar"Case 4aMonth = "Apr"
SQL
Code:Function sql_InsertClient(Client_Name,Rep_ID,Active,Client_ Since,Standard_Rate,Address1,Address2, _City_State_Zip,LiveSite_URL,Devsite_URL,Contact_Na me,Contact_Phone,Contact_Email,Contact2_Name, _Contact2_Phone,Contact2_Email,HostingPeriod,Domain Period,Notes)dim sqlsql = "INSERT into tbl_clients (client_name,rep_id,active,client_since,standard_r ate,address1," & _"address2,city_state_zip,livesite_url,devsite_url,c ontact_name,contact_phone,contact_email,contact2_n ame," & _"contact2_phone,contact2_email,hostingperiod,domain period,notes) VALUES (" & _"'" & Client_Name & "'," & _Rep_id & "," & _Active & "," & _"" & DB_DATEDELIMITER & MediumDate(Client_Since) & DB_DATEDELIMITER & "," & _Standard_Rate & "," & _"'" & Address1 & "'," & _"'" & Address2 & "'," & _"'" & City_State_Zip & "'," & _"'" & LiveSite_URL & "'," & _"'" & DevSite_URL & "'," & _"'" & Contact_Name & "'," & _"'" & Contact_Phone & "'," & _"'" & Contact_Email & "'," & _"'" & Contact2_Name & "'," & _"'" & Contact2_Phone & "'," & _"'" & Contact2_Email & "'," & _"" & DB_DATEDELIMITER & MediumDate(HostingPeriod) & DB_DATEDELIMITER & "," & _"" & DB_DATEDELIMITER & MediumDate(DomainPeriod) & DB_DATEDELIMITER & "," & _"'" & Notes & "')"sql_InsertClient = sqlend Function
Thanks in advance for any pointers.
I've been studying unbound for quite awhile and i've build to forms but i seem to get stumped with datatype mismatch. I've tried everything that i would possibly know. I guess i'm just to new in access to try this but it's the only thing that works for what i'm trying to do can some1 please provide me some assistance. I've attached both forms. YOUR HELP WILL BE GREATLY appreciated.
Now the problem, when adding or editing info i get datatype mismatch -21233 some number of that sort....
Thanks...
Eddie...
hi,
i'm trying to locate a record by id using a recordset clone. when i debug the code, i get a "type mismatch" error...
what am i doing wrong??/
here is the code:
Code:Dim rsCur As RecordsetDim strRecord As StringDim Bookmarkstr As StringstrRecord = Me.List26.ValueSet rsCur = Me.RecordsetClonersCur.Find "id =" & strRecord Bookmarkstr = rsCur.BookmarkMe.Bookmark = Bookmarkstr
any ideas??
thanks
*j
Dear All:
I am trying to print letters from access using a command button. It works well, but when I try to print the displayed record, I get "Data type mismatch in criteria expression".
The primary key is a 9-digit number where some have a leading zero. I have made this as text so I do not loose the leading zero.
Here is the code when the command button is pressed:
Private Sub Command1315_Click()
Dim strWhere As String
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print" _
, vbInformation, "Select a Record"
Else
'The Student ID Number is the name of the Primary Key
strWhere = "[STUDENT_ID] = " & Me.[STUDENT_ID]
DoCmd.OpenReport "HONORS_LETTER", acViewPreview, , strWhere
End If
End Sub
Thanks for all the help.
Dion
I don't understand why I'm getting this message.
I have a form that is based on a parameter query. The user inputs an item number and the form opens in edit mode. The user clicks the "duplicate" command button and then changes the item number. I've incorporated GHudson's "A Better Mouse Trap" logic and when clicking "save" I get the type mismatch error.
I don't understand why Access is not returning the 3022 duplicate entry error.
When I click "debug" Access highlights the line as shown below:
Err_bSave_Click:
If Err = 2046 Then 'The command or action Undo is not available now
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_bSave_Click
End If
End Sub
Any and all assistance is appreciated.
Randy
Hi,
I'm having some problems with a simple query and it's driving me nuts. The invoice numbers in our system have hyphens in them, and I'm trying to find invoice numbers whose part before the hyphen matches a number I enter. Here's my SQL:
SELECT dbo_NIHB_ClaimLog.InvoiceNum, Left([InvoiceNum],InStr([InvoiceNum],"-")-1) AS InvoiceNumLeft
FROM dbo_NIHB_ClaimLog
WHERE (((dbo_NIHB_ClaimLog.InvoiceNum) Is Not Null) AND ((Left([InvoiceNum],InStr([InvoiceNum],"-")-1))=23316));
I'm getting a "Data Type Mismatch In Criteria Expression" error, and I can't figure out why. I've wrapped a Clng() around the Left() function, but that doesn't help either.
Am I missing something here?
Okay, I apologize, I'm not extremely Access knowledgeable so bear with me.
I have an Access database I created which I thought was working okay, then I realized it was missing one vendor because we haven't started writing checks to that vendor yet and probably won't for a while.
I am using a make-table-query to create a table that combines data from two linked tables. The two linked tables have a matching field (VendorID) one linked table is "History" and the other table is "Master" (these tables are SQL data from MS Great Plains if you need to know that).
Now my problem ... not every VendorID in Master table is present in History table.
I first created the join statement to say use all records in History and only those records in Master that match. This works without any error statements, however, I discovered there is a significant field of detail that is being omitted because a VendorID is in Master, but since no checks or invoices have been created for that VendorID it does not exist in the History table yet.
I decided to change the join properties to say "include all records in Master and only those records in History that agree". When I try to run the query I receive the error message "data type mismatch". I feel certain it is because several of the fields have no information to draw from on the vendors that don't exist in the History table.
What I need the query to do is go ahead and use the information from the Master table and when there is no matching VendorID in the History table use a zero for those fields.
I used to program and know a little C+. I understand "if" statements and feel like a sql if statement could work around this (for example, if History.VendorID does not exist zero, else History.[fieldname], but I don't know how to go about doing it. Any help for a novice would be much appreciated. Thanks in advance