Data Type Mismatch and Replace Function

[lovelornloser]

Our database I created, which can link up to the main frame database is requiring use of another Team's data. If only they stuck to the rules and kept all the data mirroring the main frame database and not be some rogue team.

The problem sounds simple, they've omitted spaces in their key data which is supposed to link up with ours. I basically need to use their report, link it with our data to give another report. I have a few options, I guess.

1. Changing our data to match theirs - this means it won't link to the main frame database
2. Manually amending theirs, or asking them to ensure accuracy is kept - it's another team, so I have no control over this, it would also be fairly time consuming.
3. Building a query and utilising the Replace function to remove spaces within our data

The 3rd is the easiest to manage; or so I thought. The problem I'm now having, by utilising the Replace function it brings up the data type mismatch error. I've tried formatting it within my query into text or a string, but neither seem to be working, and now I'm stuck.

Are there any further options I could try?
I have little to no knowledge of VBA, so I would rather steer clear of it, if I can.

Many thanks in advance!
:banghead:

 

[Brianwarnock]

Cannot understand why you should get a data type error as Replace returns a string

Brian

 

[lovelornloser]

I honestly have no idea, what I tried doing as a work around was to use the replace function on both (as this would surely make them the same even if the function wasn't needed for one table). Same error. I tried formatting to text after this, too - no dice.
All I am sure of is that when I remove the replace function it works or use a different field to create a relationship with, but of course, I'm missing valuable data due to the omittance of the spaces.

The strange thing is, it kind of works with the replace function, but within a few sections, it feels a sharp slap across the face, shows the error warning and then changes everything to "#NAME?" - annoying, to say the least.

 

[jdraw]

It sounds very much like a lack of discipline generally. It isn't clear from the posts what the business is. However, if you are a viable busiiness, someone in management should recognize the "lack of standards, roles and responsibilities, and accountabilities".

Seems like you have some sort of "corporate" ( at least beyond workgroup) data that you are "trying to share".

Your problem is not Access nor mainframe etc - it is a lack of data management for the "business good".

If, you have a viable business, finance will likely discover the problem with sales/stock/order delivery type errors. If you have data on which your business depends, and you do not have any mechanism to ensure the integrity of that data, you are on a downward spiral. If you don't see it, or choose not to, your clients or competitors will.

Who is in charge of data management? Get that question out front and into your "scope of things" and quickly the issue will get focused and resolved.

Good luck.

 

[Brianwarnock]

is it possible that they have some hidden characters in their fields?

brian

 

[lovelornloser]

If you're looking at things within that respect, then the answer would be that everything is almost locked within each department's bubble. With an organisation which has over 10,000 employees it's difficult to share information and ensure that data is of correct match. Basically, each department will manage their own data their own way, this data will be similar to the main organisation database, but there's no way to currently validate and ensure that all information is completely correct without major investment - not happening. Sadly, at times like this where information needs to be shared, it might fall by the wayside if they don't follow the same process as us - we don't manage them, so we have no authority to guide them into a better working practice.

Though I do agree with you, a single entity should be utilised, but sadly I doubt this will be the case here.

The good thing is that it's only one field that I need from them to ensure the department I'm working for doesn't have to over work in order to ensure we have the correct figures, but due to a small oversight at their end, it's causing Monday's headache for me.

 

[lovelornloser]

is it possible that they have some hidden characters in their fields?

brian

I've checked and all characters are accounted for. The file they have sent over is an excel file, which I have working via a link - I basically needed to create a work around for the department I work for to be able to utilise the report being sent, temporarily import it, and compare it with our data to be able to see our absorption rate. The team are only able to enter data, but I need to plan for when I'm not around so they can still get the data.

 

[jdraw]

Have you ever sat with them (manager to manager) to define the interaction between your groups as far as the data is involved.

Someone should be able to tell you the format/valuation of the data involved.
Have you considered linking to the "mainframe data"?

I worked in corporate environment and evolved an "Environments approach" where we had Corporate computing/database; Reporting Databases which were "point in time" extracts from Corporate; Divisional/departmental data bases that took copies of corporate reference tables (as read only) for local decision support and analysis.

Seems you are needing a copy of a table from the mainframe for your diivisional use, but that's just a guess on my part.

You can't change a 10000 employee company overnight, or possibly ever. But you can try to resolve your immediate issue between 2 groups.

Seems you need someone from the mainframe group to recognize there is an issue. Then to work with him/her/them to resolve the issue to the satisfaction of both of your groups.

Oops: Your post #7 came in while I was typing.

 

[lovelornloser]

Have you ever sat with them (manager to manager) to define the interaction between your groups as far as the data is involved.

Someone should be able to tell you the format/valuation of the data involved.
Have you considered linking to the "mainframe data"?

I worked in corporate environment and evolved an "Environments approach" where we had Corporate computing/database; Reporting Databases which were "point in time" extracts from Corporate; Divisional/departmental data bases that took copies of corporate reference tables (as read only) for local decision support and analysis.

Seems you are needing a copy of a table from the mainframe for your diivisional use, but that's just a guess on my part.

You can't change a 10000 employee company overnight, or possibly ever. But you can try to resolve your immediate issue between 2 groups.

Seems you need someone from the mainframe group to recognize there is an issue. Then to work with him/her/them to resolve the issue to the satisfaction of both of your groups.

Oops: Your post #7 came in while I was typing.

Extracting from the main server will never happen - our information team don't like issuing reports which can then be uploaded into our own databases. A lot of the information we require would be more adhoc things, more down to team workloads etc - basically, the few that know how to use access, will use our own databases to store and sort the information we require to use.

I understand where you're coming from - the data from their database was originally created by myself, but sadly they've carried on collecting information and missing out a space for (more than) a few. So the datatype at point of transfer to us isn't really an issue, it's the fact that the data they have collected doesn't match ours, or the main server's. The only work around for us is to remove the space they have missed in theirs in a query so they correctly relate, but alas, I get that error when using the replace function.

 

[jdraw]

So someone is re-entering your data - one way or another.

I have experienced issues with differing data being used to support decision makers. Bad decisions generally cause "higher ups to question things". Once you point out that several sources of "seemingly the same data" is the root of the issue. Standards get some priority.
We used it to "corporate advantage" and many issues got resolved.

Good luck with your efforts.

 

[lovelornloser]

Just so I am clear, there is no work around on this - their data needs to match ours exactly, and the replace function will not help me?

 

[jdraw]

Show us the sample data involved.

 

[Brianwarnock]

You say that there is only one field that it is not working on, so perhaps if we had a sample of both sets of data we might just come up with a reason and suggestion.

Brian

 

[lovelornloser]

I hope I'm doing this right:

I have two tables.

The first is a linked excel sheet
Site, Date, Code, Name, Auth By, Action Taken & Reason for change.

These are all text fields, 255, except for the date

My table titled codes has similar data with a few added numeric fields

The query is:
Date and Code from their table
Site, AMPM, Status, Days, New, Follow Up & Location from my table

The query works when I link their code to my code, the problem is that some of their codes are missing the a space in the middle.

So I've turn my table into query with Code 1: Replace(

," ","") to remove the spaces.
Now when I link their Code to my Code 1, I get the error.
At first I thought it might be to do with the two fields being named slightly different, so I placed their linked table into a query too with the same Replace function to ensure all data types are mirrored, but still no luck.
 
I guess an alternative, which might be a longer way around is to create an update and make-table query and compare it to that, though I'm trying to not confuse the end user too much.

 

[Brianwarnock]

So what we need to see if we can help is a spreadsheet with their codes, and an mdb containing a table of your codes.

Brian

 

[lovelornloser]

Sadly our internet is restricted to which no file uploads can take place
I think I would have more rights living in North Korea!
Thanks for your help though. I'm going to try the create table method and see how that goes

 

[Brianwarnock]

Well I've messed about but cannot get your error, actually I can if the spaces were in the spreadsheet instead of the Table AND the codes are actually numbers AND one of the codes is a pure number, ie no spaces, then this comes across the Link as #num and causes the error, but that is not the way it is for you so without access to a sample I cannot help.

Brian

 

[lovelornloser]

Thanks for trying Brian
I've informed the team of the alternatives on how we can run with the data - I'll await their response

Just to let you know, the code will normally look something like this "AP 1A" (theirs will look like "AP1A") or "APA1B" and will always start with a text character, so this should always default to text, as far as I know...

Thanks again, Brian